Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Matrix conditional formatting if value is lower then previous year then red

Hey guys, I have a matrix which displays  count of shipments per month for 2018 and 2019 so for example the row for june will contain 2 numbers that of 2018 and 2019.   Now if the count of shipmen...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Managed to fix it by instead of count using sum in the formale since my table already"counts" the values. 

    Condittional formatting =
    SWITCH (
    TRUE (),
    SUM('Export'[COUNT] )
    < CALCULATE (
    SUM('Export'[COUNT] ),
    SAMEPERIODLASTYEAR ( DateTable[Date])
    ), "#ff8800")
    final result
    Extra credit to MFelix  for all the help provided!
MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi Anonymous ,

 

This calculation is based on the year basis so because it uses sameperiodlastyear so the needs is not only to change the signal.

 

Don't know how you have your model setup but you should use something similar to:

CALCULATE (
SUM('Export'[COUNT] ),
FILTER (ALL(DateTable), DateTable[Date] >= Min(DateTable[Date]) - 7 && DateTable[Date] <= Min(DateTable[Date]) - 1
)

 

I'm assuming you are using dates to the week detail on your visualization.

 

If not can you please share the sample of your setup.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for your reply MFelix ! Yes yes I changed the code to suit the weekly period as well so here is a summary of what I'm trying to do

 

So, I am trying to remove the formatting from the first column. This is essentially weekly quantity of a different products, with products on x axis and weeks on y axis. The idea is to highlight the cell if the value is more (green) or less (pink) than the previous week. I created this measure and applied as field value in conditional formatting.


format =
SWITCH (
TRUE (),
SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",
SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")

 

 

 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    Can you share a sample of your data model?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      MFelix for some reason Power BI is not allowing me to post it here nor am I able to send you a private message. Please advise

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous

         

        To share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

        If the information is sensitive please share it trough private message.