Forum Discussion

jvbs's avatar
jvbs
Helper I
6 years ago
Solved

Conditional Formatting using Total as Params

Hello everyone,   I need to implement a conditional formatting on this table where the FPD % cell is compared to it's total, like in the example below, where the 17,9% must be compared to 10,1% and...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi jvbs ,

     

    In your scenario, ALL is the key to achieve the goal.I have created a sample for your reference. 

    % FPD30 = 
CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[val] = "y" ) )
    / CALCULATE ( SUM ( 'Table'[value] ) )

    Measure = 
VAR a =
    CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[val] = "y" ) )
        / CALCULATE ( SUM ( 'Table'[value] ) )
VAR b =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[val] = "y" )
    )
        / CALCULATE ( SUM ( 'Table'[value] ), ALL ( 'Table' ) )
RETURN
    IF ( a >= b, "green", "red" )

     

    For more details, please check the pbix as attached. If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Create a measure like

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")

 

Then go to Visualization pane -> Format -> Conditional format -> Background color(on) -> Advance Control -> Choose field Value and choose this measure

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

jvbs's avatar
jvbs
Helper I
6 years ago

Thanks for your answer amitchandak , but in my case, the number "170" should be dynamic, the 10,1% is not fixed. I tried to put a measure in it's place but it doesn't work at all 😞

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi jvbs ,

     

    In your scenario, ALL is the key to achieve the goal.I have created a sample for your reference. 

    % FPD30 = 
CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[val] = "y" ) )
    / CALCULATE ( SUM ( 'Table'[value] ) )

    Measure = 
VAR a =
    CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[val] = "y" ) )
        / CALCULATE ( SUM ( 'Table'[value] ) )
VAR b =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[val] = "y" )
    )
        / CALCULATE ( SUM ( 'Table'[value] ), ALL ( 'Table' ) )
RETURN
    IF ( a >= b, "green", "red" )

     

    For more details, please check the pbix as attached. If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.