04-21-2017 06:36 AM
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?
04-22-2017 06:15 AM - edited 04-22-2017 06:18 AM
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: