Forum Discussion

ashishd's avatar
ashishd
Advocate II
2 years ago

Conditional formatting for Period over Period change in a Matrix

Hello friends,

 

Sorry, I am unable to share the PBIX file for this request but I think that this is a regular requirement and experts in this forum would be able to provide their kind guidance for the same.

 

The period in the column can be changed to Month/Quarter/Year and even the values displayed can be dynamically changed with the parameters.

The task is to color code the values in the next period based on their increment/decrement. So, it is basically color code for MoM/QoQ/YoY change (or %change) for a given row.
The Quarter is a text field here because the display format required is "Q1-2024".

Please suggest how to go ahead here.

 

 

Thank you!

Ashish

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    ashishd , you can use measure based conditional formatting

     

    if you are using field parameters refer

    Field Parameters- Conditional Formatting
    : https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

     

    You can measure like

     

    selectedAxis = maxx(FILTER('Axis Slicer', 'Axis Slicer'[Axis Slicer Order] = SELECTEDVALUE('Axis Slicer'[Axis Slicer Order])), 'Axis Slicer'[Axis Slicer])

    Color = Switch( True() ,
    selectedAxis = "Month", Switch(True() , [MOM %] >0 , "Green", "Red") ,
    selectedAxis = "Quarter Year", Switch(True() , [QOQ %] >0 , "Green", "Red") ,
    Switch(True() , [YOY %] >0 , "Green", "Red")
    )

     

     

    Use the color measure in conditional formatting using field value option

     

     

    • ashishd's avatar
      ashishd
      Advocate II
      2 years ago

      Hi amitchandak  - Thank you for the reply.

       

      Sorry, I seem to have missed asking one part of it. The first question should have been how to get the PeriodoverPeriod change with the above matrix layout as the period is dynamic and not all the period are of date type (quarter in my case is text, month is date and year is numeric).
      So, I don't want the %changes to be displayed but I want the %changes values for color coding the raw values in a row.
      Your above solution will come after I achieve this first step. Any guidance on this?

      Regards,

      Ashish

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, ashishd 

         

        You can try the following methods. Add a column to the calendar table about quarterly sorting.

        Quarter Number = YEAR([Date])*100+QUARTER([Date])

        Measure = 
Var _Sum1=CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Date','Date'[Quarter Number]))
Var _Sum2=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Date'),[Quarter Number]=SELECTEDVALUE('Date'[Quarter Number])-1))
RETURN
DIVIDE(_Sum1-_Sum2,_Sum1)
        Color measure = IF([Measure]>0,"Green","Red")

        Is this the result you expected?

         

        Best Regards,

        Community Support Team _Charlotte

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