Forum Discussion
Table subtotal is incorrect
Hi All,
I have created a matrix table shown as below. However, i couldnt get the correct the subtotal and total.
Remarks: blue text are basically measures.
| Region | Market | Type | Meeting Name | CPDD | <200 | >200 | Compliance (CPDD < 200) |
| MEA | Gulf | Internal Sales-Marketing meeting | A | 66.08 | 1 | 0 | 100.00% |
| MEA | Gulf | Internal Sales-Marketing meeting | B | 54.99 | 1 | 0 | 100.00% |
| MEA | Gulf | Internal Sales-Marketing meeting | C | 30.29 | 1 | 0 | 100.00% |
| MEA | Gulf | Internal Sales-Marketing meeting | D | 12.37 | 1 | 0 | 100.00% |
| Total | 9.93 | 1 | 0 | 100.00% |
I have tried the formula for IF(HASONEVALUE but i couldnt get the correct result as well. Could anyone please help me on this? Your help is kindly appreciated.
Hi, Anonymous
According to your description, I suggest you to use the Summarize() function to define a new table to get the correct total value, you can try to create new measures for the measures [<200], [>200], and [Compliance (CPDD < 200)] like this:
<200_new= var _new=SUMMARIZE('Table','Table'[Region],"_value",[<200]) return IF(HASONEVALUE('Table'[Region]),[<200],SUMX(_new,[_value]))>200_new= var _new=SUMMARIZE('Table','Table'[Region],"_value",[>200]) return IF(HASONEVALUE('Table'[Region]),[>200],SUMX(_new,[_value]))Compliance (CPDD < 200)_new= var _new=SUMMARIZE('Table','Table'[Region],"_value",[ Compliance (CPDD < 200)]) return IF(HASONEVALUE('Table'[Region]),[ Compliance (CPDD < 200)],SUMX(_new,[_value]))And you can get what you want.
You can also refer to these links:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
https://community.powerbi.com/t5/Desktop/How-to-show-total-row-as-sum-of-distinct-count/td-p/1416468
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Anonymous , This may be because you used row context.
refer https://www.youtube.com/watch?v=ufHOOLdi_jk
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- v-robertq-msft
Community Support
Hi, Anonymous
According to your description, I suggest you to use the Summarize() function to define a new table to get the correct total value, you can try to create new measures for the measures [<200], [>200], and [Compliance (CPDD < 200)] like this:
<200_new= var _new=SUMMARIZE('Table','Table'[Region],"_value",[<200]) return IF(HASONEVALUE('Table'[Region]),[<200],SUMX(_new,[_value]))>200_new= var _new=SUMMARIZE('Table','Table'[Region],"_value",[>200]) return IF(HASONEVALUE('Table'[Region]),[>200],SUMX(_new,[_value]))Compliance (CPDD < 200)_new= var _new=SUMMARIZE('Table','Table'[Region],"_value",[ Compliance (CPDD < 200)]) return IF(HASONEVALUE('Table'[Region]),[ Compliance (CPDD < 200)],SUMX(_new,[_value]))And you can get what you want.
You can also refer to these links:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
https://community.powerbi.com/t5/Desktop/How-to-show-total-row-as-sum-of-distinct-count/td-p/1416468
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
