Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Best Regards,
Giotto Zhi
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
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:
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.
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
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:
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |