Hierarchy Not displaying subtotal correctly

[dc52]

Hello,

I am working on a matrix visual for college programs where I am hoping to divide up students by a hierarchy of Schools and Majors but the subtotals for Schools is displaying incorrectly. Any ideas what it may be?

[dc52 · Accepted solution]

Hello,

Here is a copy of the file with the private data removed. 

Thank you so much!
https://drive.google.com/file/d/1YDL4PAs9LLszekYi2rgn2vmJg8VW3v_N/view?usp=sharing

[Anonymous]

Hi @dc52 ,

Check that the way measure aggregates the data is consistent with the hierarchy.
By the way, mention if there are levels in your hierarchy that have no data. For more information, see:
Create a matrix visual in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dc52]

Thanks for the response!
So do subtotals for hierarchies not add themselves up automatically? Is it something I need to create a new measure for and then add to the visual?

[Anonymous]

Hi @dc52 ,

Subtotals for hierarchies add themselves up automatically. it is worth reviewing the data model relationships and the DAX measures to ensure they are set up to handle the hierarchy levels as intended.

If you do not mind, could you show me some detials about your Matrix?

You can put your files in a dropbox, onedrive or googledrive folder and share the link with the community. Please provide more details with your desired out put and pbix file without privacy information.

Please refer to the related links for details:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,

Ada Wang

[dc52 · Accepted solution]

Hello,

Here is a copy of the file with the private data removed. 

Thank you so much!
https://drive.google.com/file/d/1YDL4PAs9LLszekYi2rgn2vmJg8VW3v_N/view?usp=sharing

[Anonymous · Accepted solution]

Hi @dc52 ,

You can modify your measure like the example below (other measures also modify like the example, you can refer to the attachement of pbix file):

Events Count by Major_ = 
VAR _school = SELECTEDVALUE(Students[School])
RETURN
CALCULATE(
    DISTINCTCOUNT('Events'[ID Name]),
    FILTER('Students','Students'[School]=_school))

And then add a new measure, put the new measure into Matrix:

Events Count by Major =
SUMX(VALUES(Students[School]), [Events Count by Major_])

Final output:

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dc52]

Im not sure this is counting correctly. while the hierarchy of subtotal is showing up and is adding up correcly, the Grand total count is not what it should be. for example,  the coaching count shown above SHOULD add up to 2303 total but shows up as 1370.

[dc52]

thank you!