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

matthewtjy's avatar
matthewtjy
Icon for Helper I rankHelper I
5 years ago

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!

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

    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)? 

    • matthewtjy's avatar
      matthewtjy
      Icon for Helper I rankHelper I
      5 years ago

      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.

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

        Is the measure formatted has percentage?