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 All,
I am trying to wrangle my data into the format required for a waterfall chart. I can calculate a waterfall chart that just shows the difference in values from zero, but I would like to include the initial value in the chart. The table below has an example of my data.
Year-Quarter | xParameter | Difference | Year Quarter First | XParameter First |
2020-Q1 | 38.21 | 38.21 | 2020-Q1 | 38.21 |
2020-Q2 | 36.99 | -1.22 | 2020-Q1 | 0 |
2020-Q3 | 37.31 | 0.33 | 2020-Q1 | 0 |
2020-Q4 | 39.09 | 1.78 | 2020-Q1 | 0 |
2021-Q1 | 38.28 | -0.81 | 2020-Q1 | 0 |
2021-Q2 | 39.03 | 0.75 | 2020-Q1 | 0 |
2021-Q3 | 39.55 | 0.51 | 2020-Q1 | 0 |
2021-Q4 | 38.41 | -1.14 | 2020-Q1 | 0 |
If I plot a waterfall chart using Year-Quarter and the difference column as shown in the table, than the waterfall chart looks OK. However, if I introduce a filter for say a year such that Year-Quarter starts at 2021-Q1, then a difference of -0.81 is calculated for 2021-Q1 and the waterfall chart start from zero whereas I would prefer this to start at 38.28, and then follow the difference column then.
The Calculation for Difference is :
Difference = CALCULATE ( [xParameter] - CALCULATE ( [xParameter], PARALLELPERIOD ( DateTable[Date], -1, QUARTER ) ) )
I have tried to introduce an adjustment to account for the first xParameter value, but this always returns to the first value at 2020-Q1. For the example above, I would want the XParameter first value to show at 2021-Q1.
XParameter First = IF ( FIRSTNONBLANK ( DateTable[Year-Quarter], [Year-Quarter] ) = FIRSTNONBLANK ( ALL ( DateTable[Year-Quarter] ), [Year-Quarter] ), [xParameter], 0 )
The Year-Quarter is in a Date Table and filtering is done using other columns in the date table e.g. Year.
Any ideas of how to change the formulae to account for filtering the Year-Quarter Column?
Thanks.
I couldn't reproduce the senario below, if possible, could you please provide some screenshot(Do mask the sensitive data) or provide a sample file?
"If I plot a waterfall chart using Year-Quarter and the difference column as shown in the table, than the waterfall chart looks OK. However, if I introduce a filter for say a year such that Year-Quarter starts at 2021-Q1, then a difference of -0.81 is calculated for 2021-Q1 and the waterfall chart start from zero whereas I would prefer this to start at 38.28, and then follow the difference column then."
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuta-msft,
see link below for an example sample file.
The waterfall chart is OK when a year isn't selected. When a year is selected, the waterfall chart starts at zero, rather than the first ash value. Hope that makes sense!
Cheers,
Anthony
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |