Skip to main content
cancel
Showing results for 
Search instead 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

Reply
emma313823
Helper IV
Helper IV

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]),
        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.

 

emma313823_1-1720621563781.png

 

2. Table with just high level year revenue

 

emma313823_3-1720621638461.png

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
v-tangjie-msft
Community Support
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.

vtangjiemsft_0-1720684324006.pngvtangjiemsft_1-1720684353330.pngvtangjiemsft_2-1720684388304.png

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. 

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
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.

vtangjiemsft_0-1720684324006.pngvtangjiemsft_1-1720684353330.pngvtangjiemsft_2-1720684388304.png

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
Helper IV
Helper IV

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. 

 

emma313823_0-1720625688787.png

 

 

 

 

PBI Test files

 

 

Emma

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.