cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OneWithQuestion
Post Prodigy
Post Prodigy

Waterfall chart adding percentages up in total column?

I am using a waterfall chart showing gross margin %.

 

The issue I am running into is that instead of showing period over period fluctuations it just keeps adding the percentages:

 

Periods  |  Percentage

1  | 15%

2  | 25%

3  |   5%

 

 

So in the waterfall chart I should see 15% then an increase should show from the 15% to the 25% then a decrease should show from 25% to 5%

 

When I place the measure on a table or grid it shows the total properly but when I use the waterfall chart it shows the total % as 15+25+5=40% 

 

It also shows each period as an increase so 15% then on a higher level 25% then a higher level 5%.

 

When I use a waterfall chart with profit $ (not %) it works properly.  Not sure what is going on here?

 

 

10 REPLIES 10
MFelix
Super User
Super User

Hi @OneWithQuestion,

 

Was looking at your question and work it out using this post but making the changes accordingly.

https://community.powerbi.com/t5/Desktop/DAX-Adding-offset-to-previous-row/m-p/71234#M29535

 

So based on that what I did this measure:

 

Previous% = 
VAR Previous = MIN(Table1[Periods])-1 
//Determines the previous period number

Var Ranking =  RANKX(ALLSELECTED(Table1[Periods]),CALCULATE(sum(Table1[Periods])),,ASC) 
//Calculates the ranking based on the period number in order to work with different period slicers

RETURN
    IF (
        Ranking= 1 ,							// Look to first Ranking and returns that period as a base value
			SUM(Table1[Percentage]),
				SUM(Table1[Percentage])-		
				CALCULATE (
					SUM ( Table1[Percentage] ),
						FILTER ( ALL ( Table1 ), Table1[Periods] = Previous ))	//Calculation of variation to previous period
						)

 

Now all you have to do is do your Waterfall with this percentages, the image below show one from the 1st period forward and two others with slicer you can check they are correctly calculated:

 

Previous_Period.png
Regards,
MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi, may i check what data type did you use for the various columns? You've created the solution I need for the exact same problem, however when I've copied the measure exactly, I get the error "The function SUM cannot work with values of type string".

May I also check which data should go into which field for the waterfall chart? I'm hopelessly getting error messages that the visual can't be displayed.

 

Thanks!

Hi @matthewtjy ,

 

This post is very old however I believe that looking at the information the columns are formatted has numbers and percentages.

 

What is the type of data you are using on your axis (text or number)? 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Oh right, I was using "text" type for the period.

 

Anyhow, got that fixed, however the measure still doesn't work as all the "Previous%" turned out to "1"s for each period for me even after copying the measure word for word.

Is the measure formatted has percentage?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



How might I format a measure into a percentage?

Just select the measure and on the home tab you can set the format to njmbet, percentage text.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Frederico
Frequent Visitor

Hello, @MFelix nice solution. Would you have a solution for something similar?

 

On my case I want the breakdown in percentage, but the breakdown is not a period. As an example, let´s say that I want to explain the variances of an index from period A to period B and in the breakdown show from which countries those variances are coming. A bit more detailed, let´s say that in January I had an index of 1% and in June I have, accumulated, 5%. The variances are coming from Germany (+3%), France(+2%), USA(+2%), Brazil(-2%) and Australia(-1%). Picture below.

 

In absolute values it works, but when I come to percentages Power Bi doesn´t give to me the right answer.

 

Thank you in advance.

 

2018-09-17_14h53_24.png

Hi @Frederico,

 

You refer that in absolute values it works but in percentage the values aren't right. How are yout calculaiting percentages?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Frederico
Frequent Visitor

I am approaching them in two different ways according to the needs. Examples below.

 

Measure% = (sum(valueA) + sum(valueB)) / sum(BaseValue))

or

Measure% = sum(valueA) / sum(BaseValue)

 

2018-09-18_11h05_46.png

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!