Show Sub Total on Row

[VAthavale]

Hello,

I need to show subtotal on following rows. 

[v-gizhi-msft · Accepted solution]

Hi,

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

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

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:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[v-gizhi-msft · Accepted solution]

Hi,

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

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

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:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[VAthavale]

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.

[v-gizhi-msft · Accepted solution]

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:

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

Best Regards,

Giotto Zhi

[VAthavale]

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.

[v-gizhi-msft]

Hi,

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

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

 

When you filter resources, it shows:

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:

And it shows:

Best Regards,

Giotto Zhi

[VAthavale]

Hello,

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

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.