Forum Discussion

chrisgehm's avatar
chrisgehm
Helper III
9 years ago
Solved

Divide 2 columns

Hi!   I've got a simple table in excel, where I have Name, Amount1 and Amount2   I need to show in PBI the diference betwen both and after that, I need to divide the diference with Amount1. Th...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    You need to use only MEASURES for this! :smileyhappy:

     

    Amt1 Total = SUM('Table'[Amount1])

Amt2 Total = SUM('Table'[Amount2])

Difference = [Amt1 Total] - [Amt2 Total]

Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )
Sean's avatar
Sean
Community Champion
9 years ago

So what should be the Value for A?

chrisgehm's avatar
chrisgehm
Helper III
9 years ago

Sean Sorry I was lookig the wrong number.

It worked fine! Thanks!!

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    chrisgehm

    Okay look in this example if you have only 1 name

    Name  Amt1 Amt2  Diff  Divide

    A        10      9        1      0.1

    A        10      8        2      0.2

    A        10      7        3      0.3

     

    If you use Columns you'll get 0.1+0.2+0.3 = 0.6

    But you actually want

    (10+10+10) - (9+8+7) / (10+10+10)

    (30 - 24) / 30

    0.2

    If you use columns all [Divide] column values will be simply added up which is not what you want!