Forum Discussion

quickbi's avatar
quickbi
Helper II
2 years ago

Color deppending previous week column

Hi,

I have a table with sales per city and week. I want to paint the week column that is better than previous in green and in red if it's negative. Is it possible to do it in PBI?

This is what i want to do in excel:

Thanks

Need Help

8 Replies

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    Hi quickbi ,
    Yes you can achieve it by conditional formatting in PBI ,
    I created a sample dataset and get this output ,

     

    You can use this measure and the plot it in FX OF YOUR COLUMN OPT IN FORMAT OPT. 

     

    Difference = 
VAR CurrentWeek = SELECTEDVALUE('DateTable'[WeekNoYear])
VAR CurrentWeekSales = CALCULATE(SUM('Order Data'[Sales]), 'DateTable'[WeekNoYear] = CurrentWeek)
VAR PreviousWeekSales = CALCULATE(SUM('Order Data'[Sales]), 'DateTable'[WeekNoYear] = CurrentWeek - 1)
RETURN 
IF(
    CurrentWeekSales - PreviousWeekSales >0 , 
    "green",
    "red"
)

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

    • quickbi's avatar
      quickbi
      Helper II
      2 years ago

      Hi,
      thanks for the answer, where do I have to put this measure, I can't reach your result.

       

      Thanks

       

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    Hi quickbi , i have a screen recording but its not gettinh uploaded. so let me tell you verbally.

     

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

     

     

     


    See this fx beside color opt in visualization pannel, click on this , you will see a conditional formatting screen.
    After that click on drop down and then chosee "field value" and type measure named here is difference.
    Thatsall.

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    quickbi , 
    for conditional formatting in table , 
    right click on this measure name , you will see opt of " conditional formatting", chose on background.

    • quickbi's avatar
      quickbi
      Helper II
      2 years ago

      Hi,

      Many thanks for your help, but I don't know why didn't let me:

       

      and the measure:

      Revenues Week Difference =
      VAR CurrentWeek = SELECTEDVALUE('Calendar'[Week Year])
      VAR CurrentWeekSales = CALCULATE([Revenues Actual], 'Calendar'[Week Year] = CurrentWeek)
      VAR PreviousWeekSales = CALCULATE([Revenues Actual], 'Calendar'[Week Year] = CurrentWeek - 1)
      RETURN
      IF(
          CurrentWeekSales - PreviousWeekSales >0 ,
          "green",
          "red"
      )
       
       
      I don't know why did not let me...no warnings, only a square border in red
       
      • manvishah17's avatar
        manvishah17
        Solution Supplier
        2 years ago

        Okay , quickbi see you have to select that drop down arrow on red borederd box , then a list will appear of all your table names and search for the Measure name "Revenues Week Difference", then select it and click "ok" . 
        Hope this helps.
        If you still have a doubt please let me know.
        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi quickbi ,

     

    Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.