Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
How can I do something like this that works with the slicers ?
Thanks in advance !
Solved! Go to Solution.
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.
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.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |