Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

clustered chart conditional formatting

Hello,

 

I want to have dynamic conditional formatting on a clustered chart depending on one condition. Let me show you the data :

MachineResultsMachine category
Asset 112%A
Asset 210%A
Asset 320%B
Asset 413%B
Asset 59%A
Asset 618%C
Asset 721%C
Asset 833%C

 depending of the machine category I have different targets

Machine categoryTarget
A10%
B15%
C20%

I would like to create a clustered chart with conditional formation depending on the category. If I use a slicer to selcet Asset 1 then the target will be 10%, I f I choose asset 8 it would be 20% etc..

I use a color KPI measure usually like this one : color KPI =  if('results'<0.2,"#3FA45B","#DC0D0E").

In this case I would need to compare the value with a conditional column but it does not work. Only measure appears in the DAX formual with IF.

If you have a solution it would be great

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello

     

    No it does not answer my question. Finally, I found the solution by creating a conditional column in the same table and creating a measure equal to the average of the value as the target is constant = calculate(average (conditional column)) then I can usie this measure in my formula.

6 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    6 years ago

    Hello Anonymous,

    I am not able to understand the issue. Can you please explain in detail?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

      My problem is really to create a conditional formation based on asset category.

       

      Instead of having  a measure like this : 

      color Level 1 = if([results]<0.2,"#3FA45B","#DC0D0E")
       
      I want to replace the 0.2 value by comparison with a column where the target is defined based on other criteria.
      Indeed, if the machine is category A => target will be 0.1 then I will see a graphic like this

      .

      If I select another asset using a slicer then I would like to have the conditional formationg also changing the value accordingly.

      See the different category in the post.

      At the end, depending on the results for example 18% if the asset is category A the bar chart will be RED but for the same value of 18 % an asset categorie C the bar chart will be greem

       
       

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

        Hi Anonymous ,

         

        We can try to created a measure to meet your requirement:

         

        Color = 
IF (
    SUM ( 'Table'[Results] )
        < CALCULATE (
            SUM ( 'Table (2)'[Target] ),
            FILTER (
                ALLSELECTED ( 'Table (2)' ),
                'Table (2)'[Machine category] = SELECTEDVALUE ( 'Table'[Machine category] )
            )
        ),
    "#3FA45B",
    "#DC0D0E"
)

         

         


        If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


        Best regards,

         

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

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello

       

      No it does not answer my question. Finally, I found the solution by creating a conditional column in the same table and creating a measure equal to the average of the value as the target is constant = calculate(average (conditional column)) then I can usie this measure in my formula.

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

        Hi Anonymous ,

         

        Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution. Would you please kindly mark your sharing solution as an answer so that it can benefit more users?

         

        If you still need help, feel free to ask here.


        Best regards,