Count values above threshold in a matrix table

[Anonymous]

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 rows as shown in below snapshot. City 1 has only one store (#2) above 90%, while Store 1 has two cities above 90% (#2, #4). Important also that the matrix works with the four filters.

Both the attached dataset and matrix table are made up numbers and don't match. I posted just first 6 rows of the data, because the full dataset exceeds the 20K allowed characters. I will try to post again in a new thread coming soon. I hope it works, if not, please see if you can help with the below.

I googled and watched youtube videos and it seems I should use summarize, summarizecolumns, addcolumns, etc, but I havent been successful in my attempts for many days already.

Thank you for your time.

GEO	Store	Quarter	Year	Category	Target	Revenue
City 1	Store 1	Q1	2022	Actuals	40.3	19.7
City 1	Store 1	Q1	2022	Best Case	40.3	19.7
City 1	Store 1	Q1	2022	Commit	40.3	19.7
City 1	Store 1	Q1	2023	Actuals	54.0	60.1
City 1	Store 1	Q1	2023	Best Case	54.0	60.1
City 1	Store 1	Q1	2023	Commit	54.0	60.1

[tamerj1]

Hi @Anonymous 
Place the following measure in the values of the matrix instead of the current one.

Above 90% =
VAR T =
    SUMMARIZE (
        'Table',
        'Table'[GEO],
        'Table'[Store],
        "@Percentage", [Percentage Measure]
    )
RETURN
    IF (
        COUNTROWS ( T ) = 1,
        [Percentage Measure],
        COUNTROWS ( FILTER ( T, [@Percentage] > 0.9 ) )
    )

[Anonymous]

@tamerj1, this is brilliant, it works! Thank you so much for the solution and swift reply. I will definitely accept it as solution. Just one improvement: the total comes as sum of each city counting. Is it possible to count the total figures independently?

[tamerj1]

@Anonymous 

I'm not sure what exactly do you mean but I'm sure it can be done. So would you please clarify with a screenshot that includes some markups?

[Anonymous]

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 · Accepted solution]

@Anonymous 
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 ) )
    )

[Anonymous]

@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. 

[Anonymous]

This works like a charm! Fantastic! Thanks a million @tamerj1. 

[Anonymous]

Unfortunately, I am not able to post the entire dataset.