cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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, @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 2
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.

Super User

@Anonymous , 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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors