Forum Discussion

OneWithQuestion's avatar
OneWithQuestion
Icon for Post Prodigy rankPost Prodigy
9 years ago

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  |  P...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

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:

 


Regards,
MFelix

Frederico's avatar
Frederico
Frequent Visitor
8 years ago

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.

 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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

    • Frederico's avatar
      Frederico
      Frequent Visitor
      8 years ago

      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)