Power BI % change based on columns.

[Anonymous]

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.  

[Greg_Deckler]

@Anonymous 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.

[Anonymous]

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%

[dax]

Hi @Anonymous , 

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.

[Anonymous]

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 

[dax · Accepted solution]

Hi @Anonymous , 

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.

[Anonymous]

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%