Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

conditional formatting in matrix when value is in rows

The matrix has values on rows where a1,a2,a3... are different measures distributed along the

8 weeks date range. I want a conditional formatting as for the date 24-01-2022. When the value for the current week (i.e. 24-01-2022) is greater than prev week (i.e. 17-01-2022) there should be a green upward arrow and when less it should show a red downward arrow. How to get this format?

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Anonymous ,

     

    Try to add the following measure and then use the values to do the contional formatting:

    Condittional format =
VAR currentWeek =
    SELECTEDVALUE ( Table[Date] )
VAR TotalWeek =
    CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table ) )
VAR CurrentWeekValue =
    SUM ( Table[Vaalue] )
VAR PreviousWeekValue =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER (
            ALL ( Table[Date] ),
            Table[Date]
                = MAX ( Table[Date] ) - 7
        )
    )
RETURN
    IF (
        currentWeek = TotalWeek,
        IF ( CurrentWeekValue - PreviousWeekValue < 0, -1, 1 )
    )

     

    Be aware that this measure only works when the fact table only has one date for each week (weekly data) if you have daily data this needs to be modified.

3 Replies

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

    Hi Anonymous ,

     

    When you refer that you want to have the  condittional formatting should be for the current week is the maximum week selected on the 8 weeks range? 

     

    Can you please 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.

     

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous , 

     

    Unfortunately, Conditional formatting of icons supports only static values for the rules. You can submit your idea on power bi ideas website.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

     

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

    Hi Anonymous ,

     

    Try to add the following measure and then use the values to do the contional formatting:

    Condittional format =
VAR currentWeek =
    SELECTEDVALUE ( Table[Date] )
VAR TotalWeek =
    CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table ) )
VAR CurrentWeekValue =
    SUM ( Table[Vaalue] )
VAR PreviousWeekValue =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER (
            ALL ( Table[Date] ),
            Table[Date]
                = MAX ( Table[Date] ) - 7
        )
    )
RETURN
    IF (
        currentWeek = TotalWeek,
        IF ( CurrentWeekValue - PreviousWeekValue < 0, -1, 1 )
    )

     

    Be aware that this measure only works when the fact table only has one date for each week (weekly data) if you have daily data this needs to be modified.