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...
Need Help
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
    1 year 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.