Having difficulty with percentages not working properly

[datadelta]

Not sure what I'm doing wrong here.  I want to show growth and decline between 2020 and 2021.  Obvioulsy this is not accurate for all data points.

Also, can I show 2021 being up in green and 2021 being down in red?

thanks. 

[Anonymous]

Good afternoon,

It sounds like you are asking how to calcualte year over year growth and how to format the values to show green for growth and red for decline.

YoY = (current year - last year) / last year

OR

YoY = (2021-2020) / 2020

Once you have your values you can go into 'conditional formatting' under Format in visualizations. Select your measure for the drop down and then select what item you want to format (in my example I am formatting the Font Color). This is my example:

This is what your visualization will look like once you apply the conditional formatting:

Please let me know if this helped.

-JID

[datadelta]

Thanks for the information on the font color, very helpful!

I just need to be able to write the formula.  I've only used the DIVIDE function.

In Excel it would simple - 2021-2020/2021.  

Thank you.

[Anonymous · Accepted solution]

Very similar in PBI. The formula would be: DIVIDE([2021] - [2020], [2020])

Where [ ] represent the intended field.

[smpa01]

@datadelta  You provided an image which can't be query by power bi to retreive the table. Ideally when you ask question, the min requirement is to produce a minimum reproducible example so that the responder can access the data and devise a solution.

There is a great post below from Greg.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...

[datadelta]

2020 =

Joe's Shop1,058,560, My Shop 2,085,669, Your Shop 885,772

2021 =

Joe's Shop 1,775,211, My Shop 5,878,332, Your Shop 559,892

I need to show the percentage of growth or decline.  I used the following:

DIVIDE('Sales'[Shops2020], 'Sales'[Shops2021])

Some of the percentages are right and some are wrong as in previous image.

Thanks.

[smpa01]

@datadelta  How does your data model look? Which table filters which? Is there a dim table that is on a star schema with 2020 and 2021? Or  Are both tables disconnected and there are no dimShop Table? Spare more details.

There are multiple way sto achieve what you want depending on how you modelled your data?

[datadelta]

I was able to solve this by adding a column and doing a simple subtraction and division.

Appreciate the assistance.

[datadelta]

Hello.  

If I were writing this formula in Excel it would be as such.  =(H3-G3)/H3.  I would have my answer.

Isn't there a simple way to write this same formula in BI?

[smpa01]

@datadelta  questions with smaple data attracts more willing responder in this forum

[datadelta]

I provided sample data.  Is that what you are asking for?