cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

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

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]),
)
RETURN
DIVIDE(SUM('Revenue Combined'[Revenue]) - __PREV_MONTH, __PREV_MONTH)

YoY%

YoY% =
VAR __PREV_YEAR =
CALCULATE(
SUM('Revenue Combined'[Revenue]),
)
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

Emma
1 ACCEPTED SOLUTION
Community Support

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.

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.

2 REPLIES 2
Community Support

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.

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.

Helper V

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

Emma

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.