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

     

     

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
Icon for Community Champion rankCommunity 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      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 ) )
    )

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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