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
VAthavale
Frequent Visitor

Show Sub Total on Row

Hello,

 

I need to show subtotal on following rows. Matrix Subtotal.PNG

2 ACCEPTED SOLUTIONS
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, I create a table to test:

71.png

Then, create a column to calculate the total amount and put it in the row fields:

Total Amount by Product = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Products],'Table'[Work Order Number]))

72.png73.png

the reason why Power BI Desktop does not calculate the sum of total amount for each [Work Order Number] is that because this column is in the rows fields and you should put it in values fields.

 

So, I put this calculated column in values field, and it shows:

74.png75.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

View solution in original post

Hi,

 

For your problem, I use a new table to test:

 

Please take following steps:

1)Create a measure to replace the column [TotalAmountByProduct]:

Total Amount by Product = SUM('Table'[Amount])

 2)Put this measure into Values field and it shows:

72.PNG

73.PNG

3)Turn off 'Word Wrap' in Format->Column headers and hide this measure under each resources, it shows:

74.PNG

 

Best Regards,

Giotto Zhi

View solution in original post

6 REPLIES 6
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, I create a table to test:

71.png

Then, create a column to calculate the total amount and put it in the row fields:

Total Amount by Product = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Products],'Table'[Work Order Number]))

72.png73.png

the reason why Power BI Desktop does not calculate the sum of total amount for each [Work Order Number] is that because this column is in the rows fields and you should put it in values fields.

 

So, I put this calculated column in values field, and it shows:

74.png75.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

Thank you for your reply. I created new column. But my requirement is as follows:

1. I need to show

i. Work Order Number

ii. Products per Work Order Number

iii. Total Amount

iv. Resources worked on it

v. Bonus per Resource

vi. Total Bonus

vii. % Loss or Profit per Work Order Number

 

Therefore I have Resources in Columns. If I add Total Amount in Values it gets devided per resource and I dont want that. Kindly help me with this.


Matrix Details.PNG

 

Hi,

 

For your problem, I use a new table to test:

 

Please take following steps:

1)Create a measure to replace the column [TotalAmountByProduct]:

Total Amount by Product = SUM('Table'[Amount])

 2)Put this measure into Values field and it shows:

72.PNG

73.PNG

3)Turn off 'Word Wrap' in Format->Column headers and hide this measure under each resources, it shows:

74.PNG

 

Best Regards,

Giotto Zhi

Thank you for your response.

How can I hide Total Amount for each resource as resources will change according to filters applied.

And how can I show % Revenue column at the end of matrix.

Hi,

 

For your first problem, please select the visual and choose its Format->Column headers->Word wrap, then turn off it:

101.PNG

Then you can drag the columns you do not want to hide them:

102.PNG 

103.PNG

107.PNG

When you filter resources, it shows:

104.PNG

And for your second problem, please create a measure:

% Revenue = SUMX('table','Table'[Unit Price]*'Table'[Amount]) / CALCULATE(SUMX('table','Table'[Unit Price]*'Table'[Amount]),ALLSELECTED('Table'))

Then, change its format:

105.PNG

And it shows:

106.PNG

 

Best Regards,

Giotto Zhi

Hello,

 

Thank you so much for quick and right support. It is helping me alot. I am thinking of displaying matrix as below.Matrix New.PNG

Here I dont want to show

1. Total Amount in front of Resources

2. And Total Bonus in front of Product is showing double (as it is calculated measure I am missing something)

Could you please help me with this.

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.