Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Creating a measure for different hierarchies

I'm working on a report where I need to create a measure that tracks progress made in 4 different phases. 

Each Attribute (column Attribute) have 4 different levels as shown in column 'Levels'. If all 9 Attributes have a level rating of 1, it means Phase 1 is achieved. If only 6 out of 9 Attributes have a level rating of 2, then 66.7% of Phase 2 was achieved. 

I created a simple measure for this that works at the lowest hierarchy 'Teams'. But when I roll up to any hierarchy that is higher, like Department or Site, the measure doesn't work because it doesn't cover everything.  The measure I have rn is 

Phase progress =
CALCULATE(DISTINCTCOUNT(Table1[Attribute])/9)

 

For example, the Attribute 'Multiskill' is at level 1 for Team L95 whereas it's level 2 for Team L35. So when we look at it in the Site view of JKV, Multiskill should actually be at level 1. And it follows the same logic for any other higher level 'cause the least level rating would need to be considered to show at a higher view where teams are lacking. So, only if ALL teams are at a particular level, the site should show they are at that level. 

Screen Shot 2023-01-31 at 4.53.46 PM.png

 

I have another measure I created for the matrix visual in the screenshot below to show what level a team or site or department is in, that tackles this problem 

Level testing =
var _date = MAX(Table1[Completion time]) - 5
var result = SWITCH(
TRUE(),
ISINSCOPE(Table1[Team]), CALCULATE(MAX(Table1[Levels]),'pbi DimDate'[DateDt] >= _date),
CALCULATE(MIN(Table1[Levels]),'pbi DimDate'[DateDt] >= _date))
return
result

Screen Shot 2023-01-31 at 5.06.34 PM.png

 

But this doesn't help me with my phase progress measure which is in the top card visuals. Ideally, I could take the level testing measure above and count the results from that and divide by 9. 'Cause that measure accounts the least level rating. But that doesn't seem to be possible so far.

 

Point is, the matrix visual count for each level and the card visual percentages need to match.

 

I've exhausted all my knowledge and resources and am not able to fugure it out! Any help would be greatly appreciated! If anything isn't clear, let me know. I can explain it better.

 

Thanks,

Sunayana 

 

 

4 REPLIES 4
Anonymous
Not applicable

 

Included a sample table here.

 

IDDateFacility TypeSiteDepartmentTeamAttributeLevels
11/25/2023BreweryJKVPKGL35Team Dynamic2
11/25/2023BreweryJKVPKGL35Leadership2
11/25/2023BreweryJKVPKGL35SKAP2
11/25/2023BreweryJKVPKGL35Multiskill2
11/25/2023BreweryJKVPKGL35Safety Culture2
11/25/2023BreweryJKVPKGL35Quality3
11/25/2023BreweryJKVPKGL35Problem Solving3
11/25/2023BreweryJKVPKGL35VPO Champion2
11/25/2023BreweryJKVPKGL35ATO4
21/26/2023BreweryJKVPKGL95SKAP2
21/26/2023BreweryJKVPKGL95Multiskill1
21/26/2023BreweryJKVPKGL95Safety Culture3
21/26/2023BreweryJKVPKGL95Quality2
21/26/2023BreweryJKVPKGL95Problem Solving2

 

The results I'm expecting are as follows - 

 

When Department slicer is applied, 

Phase Progress should be as follows - 
Phase 1 - 9/9 = 100% (because both teams are at least at a 1 or above
Phase 2 - 8/9 = 89.8% (because even though Multiskill is 2 for L35, it is 1 for L95 and at the higher view, they would consider their teams are at 1 since not all are at 1. So basically when multiple teams are involved, consider the least value for that attribute. Also because ATO is at 4, which means they completed level 2 already)

Phase 3 - 1/9 = 11.1% (Because ATO is at 4 and they would have completed 3 to go to 4. But no other attributes are in that level for both the teams)

Phase 4 - 1/9 = 11.1% (Because of ATO)

 

I hope this clears it up a little bit more. This is the same logic that will be applied as we go up the hierarchy.

Ashish_Mathur
Super User
Super User

Hi,

Share data in a simple Table (in a way that i can paste that in an MS Excel file) format and show the expected result in a simle Table format itself.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi, 

 

Thanks for the response! I've added a table in the comment below.

Show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.