Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I need your help about an aggregate in a cross table.
You an see in that example that I have several invoices numbers for only one order.
Therefore, all my lines are duplicated with the same content.
Do you know how can I aggregate the invoice number, to have in this example a result like that :
Thanks lot for your help
It is. Use aggr() in lieu of total <[Order Number]> as follows in the formula of a calculated dimension
=aggr( concat( Distinct [Invoice Number] , ', ' ), [Order Number])
HI!
You can try using this on your expression:
=Concat(Distinct [Invoice Number],',')
There's attached an example using this.
Regards,
Gabriel
You can delimit the invoice numbers with the concat() function. Probably something like this
=concat( Distinct total <[Order Number]> [Invoice Number] , ', ' ) in an expression
This can be done in a chart expression, or you can build the data model this way in the load script.
Doing the latter will eliminate any duplicate records from the data model and you won't have to repeat any chart conditions when dealing with the duplicates elsewhere in the dashboard.
Otherwise you can do a NULL filter in the expression to get rid of the duplicates. Post the spreadsheet data and i'll post formula.
Hello,
Thanks to both of you.
Your methods works fine with the concat as expression.
But it is possible to do this for a dimension ?
Thank you again !
It is. Use aggr() in lieu of total <[Order Number]> as follows in the formula of a calculated dimension
=aggr( concat( Distinct [Invoice Number] , ', ' ), [Order Number])
It works very fine.
Thanks lot !
Thanks this document is useful !
Hi,
You can also do aggregation at the script level.
Example:
Load Section,Order_Number,Order_Designation,Order_Supplier,Order_Date,Concat(Invoice_Number,',')
resident [table_name]
group by Section,Order_Number,Order_Designation,Order_Supplier,Order_Date;
Thanks ,
Geeta