cancel
Showing results for
Did you mean:
Highlighted
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?

4 REPLIES 4
Highlighted
Super User V

## Re: Waterfall chart adding percentages up in total column?

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

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:

Regards,
MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Frequent Visitor

## Re: Waterfall chart adding percentages up in total column?

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.

Highlighted
Super User V

## Re: Waterfall chart adding percentages up in total column?

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

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Frequent Visitor

## Re: Waterfall chart adding percentages up in total column?

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)

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors