Forum Discussion

c_tonetti's avatar
c_tonetti
Frequent Visitor
5 years ago
Solved

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. 

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

 

Thanks in advance ! 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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)

    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. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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)

    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. 

    • c_tonetti's avatar
      c_tonetti
      Frequent Visitor
      5 years ago

      Hi Anonymous 

       

      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 🙂