cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
c_tonetti
Frequent Visitor

How to construct a waterfall chat with difference year over year that works with the slicers

Hi eveyrone ! I have a table called Pilot File and it has several columns (brand, zone, division, engineer, product family, metier) and rows (33k). Another table is used for the filters (brand, zone, division, engineer, product family, metier). I created a "clef" column in each table which concatenates all columns, so I was able to correlate both tables. 

 

Now I want to construct a waterfall chart with difference year over year. I used this formula to create a new table: 

Waterfall chart = GENERATESERIES(2011,YEAR(TODAY()),1)
and new columns:
Number of Pilots = CALCULATE(COUNT('Pilote File'[Sample number]),FILTER('Pilote File','Pilote File'[Year]='Waterfall chart'[Column]))
Difference1 = (CALCULATE(MAX('Waterfall chart'[Number of Pilots]),FILTER(ALL('Waterfall chart'),'Waterfall chart'[Year]=EARLIER('Waterfall chart'[Year])-1)) - 'Waterfall chart'[Number of Pilots])
 
The result is exactly what I need (see photo), except that it doesn't work with my filters. 
c_tonetti_0-1623311004175.png

How can I do something like this that works with the slicers ?

 

Thanks in advance ! 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @c_tonetti 

You want to get a dynamic result in your water fall chart. However your Difference column is a calculated column.

Try to build measures to achieve your goal.

Number of Pilots = 
VAR _NumberofPilots = CALCULATE(COUNT('Pilote File'[Value]),FILTER(ALLSELECTED('Pilote File'),'Pilote File'[Year]= MAX('Waterfall chart'[Year])))
Return
_NumberofPilots
Difference = 
VAR _Difference = SUMX(FILTER(ALLSELECTED('Waterfall chart'),'Waterfall chart'[Year] = MAX('Waterfall chart'[Year])-1),[Number of Pilots])-[Number of Pilots]
Return
_Difference

Result in my sample.

Category slicer is built by category column in Pilote File(unrelated table)

3.png

4.png

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

Hi @c_tonetti 

You want to get a dynamic result in your water fall chart. However your Difference column is a calculated column.

Try to build measures to achieve your goal.

Number of Pilots = 
VAR _NumberofPilots = CALCULATE(COUNT('Pilote File'[Value]),FILTER(ALLSELECTED('Pilote File'),'Pilote File'[Year]= MAX('Waterfall chart'[Year])))
Return
_NumberofPilots
Difference = 
VAR _Difference = SUMX(FILTER(ALLSELECTED('Waterfall chart'),'Waterfall chart'[Year] = MAX('Waterfall chart'[Year])-1),[Number of Pilots])-[Number of Pilots]
Return
_Difference

Result in my sample.

Category slicer is built by category column in Pilote File(unrelated table)

3.png

4.png

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

Hi @RicoZhou 

 

First of all, thank you very much for your answer! It worked really well, except that the values had opposite signs, so I made a slight modification:

 

Difference =

VAR _Difference = -(SUMX(FILTER(ALLSELECTED('Waterfall chart'),'Waterfall chart'[Year] = MAX('Waterfall chart'[Year])-1),[Number of Pilots])-[Number of Pilots])

Return

_Difference


However, I didn't understand it totally. I thought your formula would calculate only the difference between 2021 and 2020, since it uses the maximum year value (2021) and max year - 1 = 2020. Could you please explain me?

For other members, I also managed to resolve the problem in a different way that might be helpful:

 

Number of pilots = CALCULATE(COUNT('Pilote File'[Sample number]))

PARALELLPERIOD = CALCULATE([Number of pilots], PARALLELPERIOD('Calendar'[Date], -1, YEAR))

Difference = [Number of pilots] - [PARALELLPERIOD]

 

Best regards,

Camila 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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!