Aggregate values in a cross table

[#1]

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

[Anonymous]

HI!

You can try using this on your expression:

=Concat(Distinct [Invoice Number],',')

There's attached an example using this.

Regards,

Gabriel

[JonnyPoole]

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.

[#4]

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 !

[JonnyPoole · Accepted solution]

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])

[#6]

It works very fine.

Thanks lot !

[#7]

Thanks this document is useful !

[Anonymous]

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