Dynamic Calculated column in matrix

[Anonymous]

Hi ,

How to create dynamic calulated(Total) Column in matrix by using the names.

For example i have two names like ABC , DEF and in future the new name will add say GHI with values.

Now i need to calculate a column with the values dynamically.

why am asking to calculate the column is i need to move the total column from right to left.

Sorry for my poor Explanation .

And thank in Advance.

Regards,

Suresh Pantra.

[rsaprano]

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

[Anonymous]

Hi rsaprano,

Thank you for your reply.

It is an unpivoted data. Can you please explain in detail.

Regards,

Suresh pantra.

[rsaprano]

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

[mussaenda]

Provide a sample data that can be copied here and show your expected output.

Thank you

[Anonymous]

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.

[jdbuchanan71]

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.

[Anonymous]

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.

[jdbuchanan71]

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

[rsaprano]

@jdbuchanan71 - this measure wouldn't automatically pick up a new column (e.g JKL) which is the requirement!

[jdbuchanan71]

In order for the new entries to get picked up you will have to unpivot them to a name value pair in the query editor.  Select all the columns that are not one of the value columns (ABC, DEF, GHI) and choose unpivot other columns.  That way when new columns come in they will get picked up by the unpivot.