Forum Discussion

swwong1's avatar
swwong1
Helper III
3 years ago
Solved

Detecting Column Subtotals in Matrix

Hi All

 

I have set some conditional formatting on my matrix based on a rule (measure) and have chosen to apply for both values and totals. The reason I want this on the totals is that I want the rows to still be highlighted when I expand the type.

 

Due to the above, the column subtotals are highlighted but I want to unhighlight this. I have used ISFILTERED to detect if its a subtotal and it works fine when no slicer value is selected (refer to photo 1)

However, the ISFILTERED doesn't work when one of the values is selected in the slicer. Can you please advise which DAX function I should use to detect this (refer to photo 2)

 

PBI File 

 

Thanks!

 

Need Help

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    try this but i'm not sure
    but you need to choose at least 2 Sports

     

     

    ИСФ =
 ПЕРЕМЕННАЯ _max1 =
     ВЫЧИСЛИТЬ ([Общий доход], ALLSELECTED())
 ПЕРЕМЕННАЯ _max2 =
     ВЫЧИСЛИТЬ ( [Общий доход], ALLSELECTED ( 'Спорт'[Спорт] ))
 ВОЗВРАЩАТЬСЯ
     ЕСЛИ ([Общий доход] = _max1 || [Общий доход] = _max2, 0, 1)
 или же 
 ИСФ = 
 ПЕРЕМЕННАЯ _max1 =
     ВЫЧИСЛИТЬ ([Общий доход], ALLSELECTED())
  VAR _has= не HASONEVALUE(Спорт[Спорт])
 ВОЗВРАЩАТЬСЯ
     ЕСЛИ ([Общий доход] = _max1||_has, 0, 1)
 -------------
 ИСФ = 
  VAR _has1= не HASONEFILTER('Таблица1 (2)'[Тип])
  VAR _has2= не HASONEFILTER('Спорт'[Спорт])

 ВОЗВРАЩАТЬСЯ
     ЕСЛИ (_has1||_has2, 0, 1 )

     

     

    you also need to make a one-to-many relationship

     

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi swwong1 ,

     

    Please try:

    Measure = IF(ISINSCOPE(Sports[Sports]),[Highlight Rows],0)

    Then use the measure to conditional formatting:

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.