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.
I am new to power bi and am slowly learning as I go.
I have several columns brought into power BI.
Eg.
2020 Vol, 2019 Vol, Vol Variance.
I want to show the absolute volume variance which is what I see, but I also want to be able to divide it into the base year to show a percentage change vs the prior year. I believe I want to do this by adding a column but I have not yet mastered the language to get it to do what I need.
Can you help.
Solved! Go to Solution.
Hi @mayork ,
You could refer to my sample for details
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mayork Not entirely certain, but perhaps:
New Column = DIVIDE([2020 Vol] - [2019 Vol], [2019 Vol])
or perhaps just
New Column = DIVIDE([2020 Vol], [2019 Vol])
If none of that helps, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi Greg, thanks for trying to help.
That didnt seem to work. What I would do normally in excel is divide the var - eg 1,729 into the base volume of 7,825 and it would give me 22% as the change.
2020 Volume | 2019 Volume | Var | % Change |
6096 | 7,825 | (1,729) | -22% |
4242 | 4,470 | (228) | -5% |
Hi @mayork ,
You could try below measure
Measure 2 = ABS(DIVIDE(MIN('Table'[2020 Volume])-MIN('Table'[ 2019 Volume ]),MIN('Table'[ 2019 Volume ])))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can anyone help with this? I can get it to work on the individual lines, but the total line (eg customer 1 and customer 2) add the two variances together instead of doing the calculation on the total as well. So for example -40 percent is incorrect, but -11 and -30 are correct. I feel like its some small thing that I am not doing. @Greg_Deckler @dax
Hi @mayork ,
You could refer to my sample for details
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The more I look at this more I realize there is just something not working how I want it to.
There are two products types that make up the line item that does not appear to have the correct % change on the total line. The matrix format is summing up the 11+30 and making it 40% instead of recalculating on the total. Is there somewhere I can indicate that it should calculate the % change at all total levels and not just add up the two lower level % change totals?
2020 volume | 2019 Volume | Volume Var | Var % | |||
Total | 25746 | 35251 | (9,505) | -40% | should be -27% - Is summing below percentages | |
Product Type 1 | 4396 | 4,913 | (517) | -11% | ||
Product Type 2 | 21350 | 30,338 | (8,988) | -30% |
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |