cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kyap1993
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.

RegionMarketTypeMeeting NameCPDD<200>200Compliance (CPDD < 200)
MEAGulfInternal Sales-Marketing meetingA            66.0810100.00%
MEAGulfInternal Sales-Marketing meetingB            54.9910100.00%
MEAGulfInternal Sales-Marketing meetingC            30.2910100.00%
MEAGulfInternal Sales-Marketing meetingD            12.3710100.00%
   Total              9.9310100.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
v-robertq-msft
Community Support
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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
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.

View solution in original post

amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.