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
6 years ago

Hi Anonymous ,

 

As you refer this should be done with a measure, however you need to take in to attention that this depends also on context of your table. Not really sure if you have a date calculation or a year column only but you need to do something similar to this (assuming you have a calendar table):

 

Condittional formatting =
SWITCH (
    TRUE ();
    COUNT ( Conditional[Quant] )
        <= CALCULATE (
            COUNT ( Conditional[Quant] );
            SAMEPERIODLASTYEAR ( 'calendar'[Date] )
        ); "#ff8800"
)

Then use this measure on the conditional value as a Field value.

 

Regards,

MFelix

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi MFelix 

    I have a date table and a shipment_date attribute which contains duplicate dates that have a relationship. Using:

    Condittional formatting =
    SWITCH (
    TRUE (),
    COUNT ( Query1[COUNT] )
    <= CALCULATE (
    COUNT ( Query1[COUNT] ),
    SAMEPERIODLASTYEAR ( DateTable[Date].[Date])
    ), "#ff8800")
     
    In advanced options of conditional formatting based on field value i get the following result:
    So somewhere the calculation is wrong only august and september 2019 should be orange
     
    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi Anonymous ,

       

      Believe that your issue is regarding the .[DAte] in your formula try the following.

       

      Condittional formatting =
SWITCH (
TRUE (),
COUNT ( Query1[COUNT] )
<= CALCULATE (
COUNT ( Query1[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date])
), "#ff8800")

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi MFelix  

         

        Nah I tried that doesn't make a difference for the result. I'm not sure what makes the higher values appear orange, it seems pretty random but it might be something in the dataset. I will have a look at it, but any suggestions?

        EDIT: forgot to mention shipment_date is date/time so for example 24/07/2018 00:00:00 can this cause issues creating a relationship with a date table? That would seem the only obvious explanation.  checked changed to date no changes