Forum Discussion

yybi123's avatar
yybi123
Helper I
5 years ago
Solved

Format value base on matrix row total

Hi, I have a matrix visual like below. How can I format the value base on the row total.  The value in this matrix is a Unit Price measure =  Sum(Price)/Sum(Unit) For example Non Premium Catego...
Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, yybi123 

    I create a demo based on you data. Please try  measures as below:

     

    Unit Price2 =
VAR a =
    CALCULATE ( SUM ( 'Table'[Price] ), ALLSELECTED ( 'Table'[Name] ) )
VAR b =
    CALCULATE ( SUM ( 'Table'[Unit] ), ALLSELECTED ( 'Table'[Name] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Price] ) / SUM ( 'Table'[Unit] ),
        ALLSELECTED ( 'Table'[Name] )
    )

    Color measure =
IF ( [Unit Price] < [Unit Price2], "tomato", "lightGreen" )

     

    Then apply the "Color measure" to conditional formatting.

    Result :

     

    Please check my sample pbix for more details.

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

yybi123's avatar
yybi123
Helper I
5 years ago

I am not too sure if this can be applied to my data. As I need to compare the value for each person by category against the total average of the category.

 

I do not have a measure that calculated the average by category ignoring the name. Do you know any dax I can achieve that? Then with that I can probably use your method above to colour code by applying rules if personal value greater than average value then green/red. 

 

Here is my data.

 

yybi123's avatar
yybi123
Helper I
5 years ago

 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, yybi123 

    I create a demo based on you data. Please try  measures as below:

     

    Unit Price2 =
VAR a =
    CALCULATE ( SUM ( 'Table'[Price] ), ALLSELECTED ( 'Table'[Name] ) )
VAR b =
    CALCULATE ( SUM ( 'Table'[Unit] ), ALLSELECTED ( 'Table'[Name] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Price] ) / SUM ( 'Table'[Unit] ),
        ALLSELECTED ( 'Table'[Name] )
    )

    Color measure =
IF ( [Unit Price] < [Unit Price2], "tomato", "lightGreen" )

     

    Then apply the "Color measure" to conditional formatting.

    Result :

     

    Please check my sample pbix for more details.

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.