Forum Discussion

Claythorne's avatar
Claythorne
Frequent Visitor
6 years ago
Solved

Daily Updating Production Value Red/Green based on Target

Hey all,

 

I'm attempting to create a measure that will compare yesterdays production/consumption to yesterdays targets. And if they are above the target, turn green with an up arrow and if below, red with a down arrow etc. I want to be able to display both the targets and the production values, but with the production values being the one that changes background color.

 

 

I'm not really sure how to go about this so if anyone could help it would be greatly appreciated!

Need Help
  • Claythorne's avatar
    Claythorne
    6 years ago

    v-lid-msft I ended up using the following measures and the conditional formatting under data label, which I was not aware of at the time. Its essentially the same as the measures you posted. Either way it ended up turning out exactly how I wanted! Thanks for the help!

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    If you plan to plot by date. You can use a day behind va;ue example

    sales =SUM(Sales[Sales Amount])

    last day Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,DAY))

     

    Diff = [sales ]-[last day Sales]

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

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

    Hi Claythorne ,

     

    We can try to use two measures in condition format to meet your requirement:

     

    HCN_Compare = SUM('Table'[HCN Consumed])-CALCULATE(SUM('Table'[HCN]),FILTER(ALLSELECTED('Table'),'Table'[Date]=SELECTEDVALUE('Table'[Date])-1))

     

    NaCN_Compare = SUM('Table'[NaCN Produced])-CALCULATE(SUM('Table'[NaCN]),FILTER(ALLSELECTED('Table'),'Table'[Date]=SELECTEDVALUE('Table'[Date])-1))

     

     


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


    Best regards,

     

    • Claythorne's avatar
      Claythorne
      Frequent Visitor
      6 years ago

      So I typed in the measures as you described:

       

      "HCN_Compare = SUM(Sheet2[HCN Consumed])-CALCULATE(SUM(Sheet2[HCN]),FILTER(ALLSELECTED(Sheet2),Sheet2[Date]=SELECTEDVALUE(Sheet2[Date])-1))"
       
      "NaCN_Compare = SUM(Sheet2[NaCN Produced])-CALCULATE(SUM(Sheet2[NaCN]),FILTER(ALLSELECTED(Sheet2),Sheet2[Date]=SELECTEDVALUE(Sheet2[Date])-1))"
       
      However when I attempt to use them in a Card or Table visualization, I receive the following error:
       
      MdxScript(Model) (7,34) Calculation error in measure 'Sheet2'[HCN_Compare]: The function SUM cannot work with values of type String.
       
       
      Essentially what I'm trying to do is display 4 Cards (HCN Target, NaCN Target, HCN Consumed, NaCN Produced) and if the consumption/production are below the targets the background turns red and green if above. I'm currently doing this in excel with conditional formatting but I'm just starting off in Power BI and trying to learn the functionality.
       
       
       
      • Claythorne's avatar
        Claythorne
        Frequent Visitor
        6 years ago

        v-lid-msft D'oh never mind I was being dumb. I forgot to change those columns to decimal numbers and thats why I was receiving an error. The measures are working as intended! Thank you for the help!

         

         

        Now is there a way I can use conditional formatting, like excel, to make it where the numbers on the cards on the bottom row are below their respective top rows, it turns the background red and if above green?