Forum Discussion

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

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 )

Community Champion

9 years ago

You will get the values per name in the Table - you have the names on the Rows! :smileyhappy:

Helper III

9 years ago

Sean it is still calculating wrong :(

Sean
Community Champion
9 years ago
So what should be the Value for A?
- chrisgehm
  Helper III
  9 years ago
  Sean Sorry I was lookig the wrong number.
  It worked fine! Thanks!!
  - 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!
BarrbC
Frequent Visitor
8 years ago
I have similar challenge. I followed the formula as in the case described. However, PBI doesn't show the results: only 2 distinct values 0 and 1. what could be the reason for that?

and what is more important I created a measure for that
DIVIDE(calculate(sum('DB Data'[Sales CY YTD $])- sum('DB Data'[Sales_PY_YTD_$])),sum('DB Data'[Sales_PY_YTD_$]),0 ) and it works.
How to reflect it as a calculated column? Equation doesn't help

Related content