cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulgalve-mrm
Helper I
Helper I

Matrix with hierarchy not calculating measures correctly

Hello - I have a matrix with hierarchy of rows.  

This is a correct total

paulgalvemrm_0-1660717917277.png

 

but here, when drilling down to another level, I'm getting this.  In this example, the Enterprise Segment Group should have 52% MEI Ranking % for FY21-Q1.  Looking at it, the Segment Group once drilled down to another level, is repeating and multiplying 591 counts to x number of rows, thus we have  591*7 =5319.  Can we use the real count(591) as variable instead?

paulgalvemrm_1-1660717960154.png

 

Thanks for the help!

 

PG

3 REPLIES 3
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

@lbendlin Apologies for the late reply. 

Here's the folder with 2 files I'm working on.
https://drive.google.com/drive/folders/1IpG25axZYFNGQ6AQ_sMRZezborb03FKn?usp=sharing

 

File #1 - Working File.pbix contains only the Segment Group and Sector fields that resulted to correct count of TPIDs.

paulgalvemrm_1-1662016857404.png

 

 

File #2 -  Not Working File contains more data as needed, so I have added more fields:  Industry, Marketing Play, Marketing Play Product. After adding more columns, it multiplied the expected TPIDs because of repeated values per row.

paulgalvemrm_0-1662016719035.png

 

Hope this helps.

 

Thanks - PG

 

Not sure if I fully understand the issue but here is a version that uses your distinctcount approach (albeit implicitly)

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.