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

     

     

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