Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI - Conditional Formatting

Hi,   Wondering if anyone is aware of how to do this. I have a series of information put into a matrix table using only three columns. Name, Month and Percentage. I need to add conditional formatti...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please create a Date Table and the relationship:

     

    Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "MonthYear", FORMAT ( [Date], "mmmm yyyy" ),
    "MonthNum", FORMAT([Date], "yyyymm")
)

     

    Then create the measure:

     

    Measure = 
var pre_month = 
    CALCULATE(
        SUM('Table'[Audit Score]),
        FILTER(
            ALL('Date'),
            'Date'[MonthYear] = FORMAT( EDATE( MAX('Date'[Date]), -1 ), "mmmm yyyy" )
        )
    )
var diff = SUM('Table'[Audit Score]) - pre_month
return 
    IF(
        diff <> BLANK(),
        IF(
            diff < 0,
            "ColoredArrowDown",
            "ColoredArrowUp"
        )
    )


    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi amitchandak 

What would be the easiest solution? My raw data is in 3 columns like the screen shot below.. The month section goes as far as January 2021 to August 2021 and I need the audit score to look at the previous audit score to determine whether it's higher or lower than the one before then display an up icon or down icon.

 

In this instance below we have three users with 3 months worth of percentages. I now need to get a higher or lower icon next to each to reflect an improvement or decrease from the previous month.

 

Kind regards,

 

Matt

 

 

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi Anonymous ,

 

Please create a Date Table and the relationship:

 

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "MonthYear", FORMAT ( [Date], "mmmm yyyy" ),
    "MonthNum", FORMAT([Date], "yyyymm")
)

 

Then create the measure:

 

Measure = 
var pre_month = 
    CALCULATE(
        SUM('Table'[Audit Score]),
        FILTER(
            ALL('Date'),
            'Date'[MonthYear] = FORMAT( EDATE( MAX('Date'[Date]), -1 ), "mmmm yyyy" )
        )
    )
var diff = SUM('Table'[Audit Score]) - pre_month
return 
    IF(
        diff <> BLANK(),
        IF(
            diff < 0,
            "ColoredArrowDown",
            "ColoredArrowUp"
        )
    )


If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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