Skip to main content
cancel
Showing results for 
Search instead 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

Reply
dc52
Regular Visitor

Hierarchy Not displaying subtotal correctly

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_1-1718120987791.png

 

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

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:

vyifanwmsft_0-1718343404321.png

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.

View solution in original post

7 REPLIES 7
v-yifanw-msft
Community Support
Community Support

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.

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?

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

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

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:

vyifanwmsft_0-1718343404321.png

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.

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.

thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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