cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Community Support

Hi, @kyap1993

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 2
Community Support

Hi, @kyap1993

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.

Super User

@kyap1993 , This may be because you used row context.

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!