Forum Discussion
Count values above threshold in a matrix table
Hello, I appreciate if you can help me with the below. I have 4 cities and 4 stores and want to build a 4x4 performance matrix and count values above 90%. The counting should be both in columns and...
Anonymous
Please tryAbove 90% = VAR T1 = SUMMARIZE ( 'Table', 'Table'[GEO], "@Percentage", [Percentage Measure] ) VAR T2 = SUMMARIZE ( 'Table', 'Table'[Store], "@Percentage", [Percentage Measure] ) RETURN SWITCH ( TRUE (), HASONEVALUE ( 'Table'[GEO] ) && HASONEVALUE ( 'Table'[Store] ), [Percentage Measure], HASONEVALUE ( 'Table'[Store] ), COUNTROWS ( FILTER ( T1, [@Percentage] > 0.9 ) ), COUNTROWS ( FILTER ( T2, [@Percentage] > 0.9 ) ) )
tamerj1, thank you for still being around.
Ideally, row "Total" should be 1 as only store 1 is above 90. The solution provided is generating 5 (the sum of above 90s in all cities). Hope that clarifies.
tamerj1
Community Champion
Community ChampionAnonymous
Please try
Above 90% =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[GEO], "@Percentage", [Percentage Measure] )
VAR T2 =
SUMMARIZE ( 'Table', 'Table'[Store], "@Percentage", [Percentage Measure] )
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( 'Table'[GEO] ) && HASONEVALUE ( 'Table'[Store] ), [Percentage Measure],
HASONEVALUE ( 'Table'[Store] ), COUNTROWS ( FILTER ( T1, [@Percentage] > 0.9 ) ),
COUNTROWS ( FILTER ( T2, [@Percentage] > 0.9 ) )
)
This works like a charm! Fantastic! Thanks a million tamerj1.
tamerj1 , the formula works except that it's extremely slow to load. It takes around 1min to load/respond when a filter is changed (from Q1 to Q2, for instance). I was trying to find alternatives, but without success.
Can you please help once more? Thank you in advance.