Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

 

GEOStoreQuarterYearCategoryTargetRevenue
City 1Store 1Q12022Actuals40.319.7
City 1Store 1Q12022Best Case40.319.7
City 1Store 1Q12022Commit40.319.7
City 1Store 1Q12023Actuals54.060.1
City 1Store 1Q12023Best Case54.060.1
City 1Store 1Q12023Commit54.060.1
  • tamerj1's avatar
    tamerj1
    3 years ago

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

     

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago
      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's avatar
        tamerj1
        Community Champion
        3 years ago

        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?