Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SureshPantra
Helper II
Helper II

Dynamic Calculated column in matrix

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.Capture.JPG

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.

10 REPLIES 10

Hi @SureshPantra 

 

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.

Hi @SureshPantra ,

 

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

 

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

 

Thank you

Hi mussaenda,

PFA

CategoryAttributeABCDEFGHITotal
Motor_vehicalsAccessories233254109
 Bikes34233289
 Cars542323100
 Trucks45121370

 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.

 

Hello @SureshPantra 

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 ,

PFA

CategoryAttributeABCDEFGHITotal
Motor_vehicalsAccessories233254109
 Bikes34233289
 Cars542323100
 Trucks45121370

 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.

@SureshPantra 

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

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.