Forum Discussion
Dynamic Calculated column in matrix
Hi Anonymous
I think the best option would be to unpivot the data so that all your labels (ABC, DEF, GHI etc) become values in a single column with the corresponding numerical values in another column.
To do this, in the query editor select any columns that you want to be fixed (in your example the 'Attributes' Column) - note that there is no need for a total column - and select 'Unpivot Other Columns'. You can rename this column e.g. to 'ColAttributes'
In the report view, create a measure which just sums your numerical values column and then in the matrix have your Attributes columns on rows, your ColAttributes column on columns and ensure you have the option for column sub-totals selected
Hi rsaprano,
Thank you for your reply.
It is an unpivoted data. Can you please explain in detail.
Regards,
Suresh pantra.
Hello Anonymous
Can you please share a sample of your data so we can copy an paste (not a picture)? That will help us answer the question.
Hi jdbuchanan,
PFA
Category Attribute ABC DEF GHI Total Motor_vehicals Accessories 23 32 54 109 Bikes 34 23 32 89 Cars 54 23 23 100 Trucks 45 12 13 70 expecting the total column which i need to use next to the attribute Column.In future for example if JKL data has came it has to calculate that data also into the total.
Regards,
Suresh Pantra.
Anonymous
The columns ABC, DEF, GHI are seperate columns in your data table? If so you can write a measure to add them together, you would just have to add in new columns as the appear in the table:
Total = SUM ( YourTable[ABC] ) + SUM ( YourTable[DEF] ) + SUM ( YourTable[GHI] )
Provide a sample data that can be copied here and show your expected output.
Thank you
Hi mussaenda,
PFA
Category Attribute ABC DEF GHI Total Motor_vehicals Accessories 23 32 54 109 Bikes 34 23 32 89 Cars 54 23 23 100 Trucks 45 12 13 70 expecting the total column which i need to use next to the attribute Column.In future for example if JKL data has came it has to calculate that data also into the total.
Regards,
Suresh Pantra.
Hi Anonymous ,
Here is a link to a PBIX that has your sample data in there with the unpivot example applied in the query editor as i was suggesting (along with the matrix)...if you edit source and add a new column to the table it will be picked up automatically.
Here is an animated gif that shows how to apply the steps.
If this answers your query, please mark the answer as accepted!
Thanks
Rishi
