Creating a measure for different hierarchies

[Anonymous]

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. 

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

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 

 

[Anonymous]

Included a sample table here.

ID	Date	Facility Type	Site	Department	Team	Attribute	Levels
1	1/25/2023	Brewery	JKV	PKG	L35	Team Dynamic	2
1	1/25/2023	Brewery	JKV	PKG	L35	Leadership	2
1	1/25/2023	Brewery	JKV	PKG	L35	SKAP	2
1	1/25/2023	Brewery	JKV	PKG	L35	Multiskill	2
1	1/25/2023	Brewery	JKV	PKG	L35	Safety Culture	2
1	1/25/2023	Brewery	JKV	PKG	L35	Quality	3
1	1/25/2023	Brewery	JKV	PKG	L35	Problem Solving	3
1	1/25/2023	Brewery	JKV	PKG	L35	VPO Champion	2
1	1/25/2023	Brewery	JKV	PKG	L35	ATO	4
2	1/26/2023	Brewery	JKV	PKG	L95	SKAP	2
2	1/26/2023	Brewery	JKV	PKG	L95	Multiskill	1
2	1/26/2023	Brewery	JKV	PKG	L95	Safety Culture	3
2	1/26/2023	Brewery	JKV	PKG	L95	Quality	2
2	1/26/2023	Brewery	JKV	PKG	L95	Problem Solving	2

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]

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.

[Anonymous]

Hi, 

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

[Ashish_Mathur]

Show the expected result very clearly.