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.
Hello Everyone,
I have a below dataset
I have created a line and clustered column chart. Pumpage_MGD is represented in columns for years in the below line and clustered column chart.
I would like to calculate variance % for 2020-2019, 2020-2018, 2020-2017, 2020-2016 and display Variance % as line in Line and Clustered column chart.
I have a slicer for years. When I select 2020 and 2019 in slicer, I should display 2020-2019 Variance % as line in Line and Clustered column chart. When I select 2020 and 2018 in slicer, I should display 2020-2018 Variance % as line in Line and Clustered column chart and so on.
I would like to show the chart as shown below
Please let me know how to achieve this.
Thank you!
Regards,
Aswini C S.
Solved! Go to Solution.
Hello @Aswini_C_S ,
Try this:
Variance % =
VAR Top1N =
VALUE (
CALCULATE (
CONCATENATEX ( TOPN ( 1, VALUES ( 'Table'[Year] ) ), 'Table'[Year], ", " )
)
)
VAR Top2N =
VALUE (
CALCULATE (
CONCATENATEX (
TOPN ( 1, VALUES ( 'Table'[Year] ), 'Table'[Year], DESC ),
'Table'[Year],
", "
)
)
)
VAR FirstSelectedValue =
CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top1N )
VAR SecondSelectedValue =
CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top2N )
RETURN
DIVIDE ( SecondSelectedValue - FirstSelectedValue, FirstSelectedValue )
This way, you can choose any two years for comparison. Or compare the older and smaller years in the selected years.
Reference: DAX – Calculating the difference between 2 selections in a slicer.
Best regards
Icey
If this post helps,then please consider accepting it as the solution to help other members find it more quickly.
Hello @Aswini_C_S ,
Try this:
Variance % =
VAR Top1N =
VALUE (
CALCULATE (
CONCATENATEX ( TOPN ( 1, VALUES ( 'Table'[Year] ) ), 'Table'[Year], ", " )
)
)
VAR Top2N =
VALUE (
CALCULATE (
CONCATENATEX (
TOPN ( 1, VALUES ( 'Table'[Year] ), 'Table'[Year], DESC ),
'Table'[Year],
", "
)
)
)
VAR FirstSelectedValue =
CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top1N )
VAR SecondSelectedValue =
CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top2N )
RETURN
DIVIDE ( SecondSelectedValue - FirstSelectedValue, FirstSelectedValue )
This way, you can choose any two years for comparison. Or compare the older and smaller years in the selected years.
Reference: DAX – Calculating the difference between 2 selections in a slicer.
Best regards
Icey
If this post helps,then please consider accepting it as the solution to help other members find it more quickly.
Hi @Icey ,
I need to calcualte average of 2018, 2019 PUMPAGE_MGD and need to display as another column in line and clustered column chart.
For Example- Average(2018-03-W1 Pumpage_MGD, 2019-03-W1 Pumpage_MGD), Average=e(2018-03-W2 Pumpage_MGD, 2019-03-W2 Pumpage_MGD), Average(2018-03-W3 Pumpage_MGD, 2019-03-W3 Pumpage_MGD) and so on..
I also need to calcualte Variance % between 2018-2019 Average and 2020 data.
Please find the below chart for your reference.
I need to add 2018-2019 avg in "year" slicer and If I select 2018-2019 Avg and 2020 in slicer, the chart should display as shown in above screenshot.
Please let me know how to solve this.
Thank you!
Regards,
Aswini
Hi @Icey
Thank you for your reply and solution.
If I don't select any year in slicer, I shouldn't see Variance%. If I select more than 2 years in slicer, I shouldn't see Variance%. I should be able to see Variance % only when I select any of the 2 years in slicer.
Is there any way to do this. Please let me know.
Thank you!
Regards,
Aswini C S.
I just changed a bit in the above DAX and It worked.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |