Forum Discussion

ERing's avatar
ERing
Post Partisan
1 year ago
Solved

Conditional formatting issue with matrix visual

I have a report with a matrix visual like below where I'm showing scores for survey results. Each of the three columns in my matrix is a measure and I'm trying to add conditional formatting to the Delta column. 

My conditional formatting is set up using a Field Value based on the measure [Color_Formatting]. The 25th percentile is -8.40, so anything below that should be formatted in red.

The issue is shown in the table visual on the left which has the Score Category (Process, Skills) and the Score Subcategory in the Rows pane. On this visual the conditional formatting is incorrect. 

In the table visual on the right I removed the Score Category (Process, Skills) from the Rows Pane leaving only the Score Subcategory. In this visual, the conditional formatting is correct.

So the question is how I can correctly show the conditional formatting in the table on the left where I have Score Category (Process, Skills) and the Score Subcategory in the Rows pane.

 

Color_Formatting = IF(Delta <= [25Percentile], "Red","Black")

25Percentile = PERCENTILEX.INC(ALL('Table'[Scorecard_Subcategory]), 'Table'[Delta],.25)

Score for Opportunities Closed = 
CALCULATE(
DIVIDE(SUM(Table[Scores]),COUNT(Table[Opportunity_ID])),
KEEPFILTERS(TABLE[Closed_Flag = 1))

Score for Opportunities Not Closed = 
CALCULATE(
DIVIDE(SUM(Table[Scores]),COUNT(Table[Opportunity_ID])),
KEEPFILTERS(TABLE[Closed_Flag = 0))

Delta = [Score for Opportunities Not Closed] - [Score for Opportunities Closed]

 

  • ERing  then add both dimensions to the ALLSELECTED, review the results now, image attahced.

    25Percentile = PERCENTILEX.INC(ALLSELECTED('Table'[Score Subcategory],'Table'[Score Category]),
    'Table'[Delta],.25)

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!

4 Replies

  • fahadqadir3's avatar
    fahadqadir3
    Solution Supplier

    ERing Review the attached pbix file and screenshot.

    Use All('Table') Instead of any specific column.

    25Percentile = PERCENTILEX.INC(ALL('Table'), 'Table'[Delta],.25)

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!! 

    • ERing's avatar
      ERing
      Post Partisan

      fahadqadir3 Thanks for your suggestion, however it does not work with my real data.

      Using just 'Table' in the ALL function produces the inccorect value for the 25th percentile.

      I think the issue has something to do with row context of the conditional formatting. 

      The correct 25th percentile value is -36.91, so with my [Color Formatting] measure, anything below -36.91 should be red, but you can see it includes other values as well.

      I've included a Sample File here if you would be able to take a look and make a reccomendation.

      SAMPLE FILE 

      25Percentile = PERCENTILEX.INC(ALL('Table'[Score Subcategory]),'Table'[Delta],.25)
      
      Color Formatting = if('Table'[Delta]<='Table'[25Percentile],"Coral","LightGray")
      

       

       



       

       




      • fahadqadir3's avatar
        fahadqadir3
        Solution Supplier

        ERing  then add both dimensions to the ALLSELECTED, review the results now, image attahced.

        25Percentile = PERCENTILEX.INC(ALLSELECTED('Table'[Score Subcategory],'Table'[Score Category]),
        'Table'[Delta],.25)

         

        Did I answer your question? Mark my post as a solution! This will help others on the forum!
        Appreciate your Kudos!