how to show negative percentages MoM or YoY as red font or cell fill

[emma313823]

Hi All,

Not sure how I can accomplish this, but in my model I created the following Measures

PrevMoRev (previous month revenue)

PrevMoRev = CALCULATE(SUM('Revenue Combined'[Revenue]),PREVIOUSMONTH(Calendar_Dates[Date]))

PrevYr Rev (previous year revenue)

PrevYrRev = CALCULATE(SUM('Revenue Combined'[Revenue]),PREVIOUSYEAR(Calendar_Dates[Date]))

MoM%

MoM% =

VAR __PREV_MONTH =

    CALCULATE(

        SUM('Revenue Combined'[Revenue]),

        DATEADD('Calendar_Dates'[Date], -1, MONTH)

    )

RETURN

    DIVIDE(SUM('Revenue Combined'[Revenue]) - __PREV_MONTH, __PREV_MONTH)

YoY%

YoY% =

VAR __PREV_YEAR =

    CALCULATE(

        SUM('Revenue Combined'[Revenue]),

        DATEADD('Calendar_Dates'[Date], -1, YEAR)

    )

RETURN

    DIVIDE(SUM('Revenue Combined'[Revenue]) - __PREV_YEAR, __PREV_YEAR)

_________________________________________________

I have two table visuals.

1. Matrix table with year and month stacked and it contains 4 years of information.

2. Table with just high level year revenue

What I would like to accomplish is to have 3 levels of color where:

1. anything 0% to -25% the cell is yellow

2. anything greather than -25% to -50% is orange 

3. anything larger than -50% is red

and I'd like to apply that to the MoM% and YoY% columns in these tables.

Not sure hwo to approach this as a novice to Power BI.

Emma

[Anonymous · Accepted solution]

Hi @emma313823 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures.

color_mom = SWITCH(TRUE(),
[MoM%]<=0 && [MoM%]>=-0.25,"yellow",
[MoM%]<-0.25 && [MoM%]>=-0.5,"orange",
[MoM%]>-0.5,"red")

color_yoy = SWITCH(TRUE(),
[YoY%]<=0 && [YoY%]>=-0.25,"yellow",
[YoY%]<-0.25 && [YoY%]>=-0.5,"orange",
[YoY%]>-0.5,"red")

(3)Set up conditional formatting and then the result is as follows.

If I misunderstand your needs, please clarify in the follow-up reply.

Best Regards,

Neeko Tang

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

[emma313823]

Below is a link a folder holding the .pbix file and the connected excel file. Please note the only worksheet connected is Revenue Combined which is highligted in the workbook. There is no sensitive info as I'm just testing out mock numbers.

https://www.dropbox.com/scl/fo/9xr0f9svlb60fpwu1lcn7/AJnsQdw3oYypcxH3JskrMFw?rlkey=18lox1852s32owd64...

In the .pbix file if you look at the Revenue MoM% & YoY% page, under formatting, cell elements, I changed the font to blue for the MoM% and YoY% columns. Both the MoM% and YoY% are measures pulled into the tables.

The screenshot below depicts the outcome I am looking for. 

PBI Test files