Rolling Up Values from Sublevel to Calculate at Higher Levels in Matrix and/or Table Visual

[ccolletti]

Hi all, and thank you in advance for all your help!

I'm working on a report for my company that I was able to get a value down at the store sublevel on if the store reached compliance marking it as a 1 or 0. Now I want to calculate this at the next levels up to a district and regional level. The query I used I found on this thread and works great: https://community.fabric.microsoft.com/t5/Desktop/Only-Total-Select-Values-in-a-Matrix-amp-Formattin... 

I've had a hard time finding something that would work in order for me to utilize the data that comes in as the Compliance value at the next levels up. Just shows up as 0.

Some example data is below, where Compliance is the value I got from your measure above. I want to calculate those and divide by the total number of stores within it's region. I'd want to eventually get the following results: East - 67%, Central - 33%, West - 50%

Region	Store	Review Count	Compliance
East		32	0
	1	20	1
	2	5	0
	3	7	1
Central		31	0
	4	2	0
	5	15	1
	6	14	0
West		50	0
	7	33	1
	8	10	1
	9	5	0
	10	2	0

Thank you very much for your help!

[ccolletti]

@v-kaiyue-msft , yes this worked! Thank you so much for your help!

[v-kaiyue-msft · Accepted solution]

Hi @ccolletti ,

Since I don't know how your measure Compliance is calculated, I'm assuming Review Count greater than 10 is 1. So the final result is a little different, but the overall logic is the same.

You can create the measure.

MEASURE =
VAR _count1 =
COUNTX (
FILTER ( ALLSELECTED ( 'Table'[Store] ), [Compliance] = 1 ),
[Compliance]
)
VAR _count2 =
CALCULATE ( COUNT ( 'Table'[Store] ), ALLSELECTED ( 'Table'[Store] ) )
RETURN
DIVIDE ( _count1, _count2 )

Set it as a percentage.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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