Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI % change based on columns.

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 w...
Need Help
  • dax's avatar
    dax
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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 VarVar %  
    Total2574635251                     (9,505)-40% should be -27% - Is summing below percentages
    Product Type 14396                      4,913                         (517)-11%  
    Product Type 221350                    30,338                     (8,988)-30%  
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      dax
      Icon for Community Support rankCommunity Support
      6 years ago

      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.