Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chrisgehm
Helper III
Helper III

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.


The difference it is calculated correctly, but when I divide, the results are not the real ones, and don't know why.


Any idea why?

 

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

You need to use only MEASURES for this! Smiley Happy

 

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

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

Difference = [Amt1 Total] - [Amt2 Total]

Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

Hi @chrisgehm

 

It's a bit hard to answer your question without a pic of the data at least.

I assume you tried to solve your problem in DAX?

@Anonymous

 

This is the excel

 

1.PNG

 

This is what I have in PBI:

 

2.png

 

Do you know why the division is not correct?

Sean
Community Champion
Community Champion

You need to use only MEASURES for this! Smiley Happy

 

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

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

Difference = [Amt1 Total] - [Amt2 Total]

Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )
Anonymous
Not applicable

I have 2 columns Im using for calcluation: Crashes, TotalInstalls

Using the syntax posted, I get an error:

Amt1 Total = SUM('PerCapita30'[Crashes])
Amt2 Total = SUM('PerCapita30'[TotalInstalls])
Difference = [Amt1 Total] - [Amt2 Total]
Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )
T

@Sean wrote:

You need to use only MEASURES for this! Smiley Happy

 

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

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

Difference = [Amt1 Total] - [Amt2 Total]

Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )


he syntax for 'Amt2' is incorrect. (DAX(SUM('PerCapita30'[Crashes])Amt2 Total = SUM('PerCapita30'[TotalInstalls])
Anonymous
Not applicable

@Sean

which would have been my solution too. You were faster Man Happy

@Sean And if I want to get the values per Name?

Sean
Community Champion
Community Champion

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

@Sean it is still calculating wrong 😞

 

1.PNG

2.png

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

 

Sean
Community Champion
Community Champion

So what should be the Value for A?

@Sean Sorry I was lookig the wrong number.

It worked fine! Thanks!!

Sean
Community Champion
Community Champion

@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!

Anonymous
Not applicable

mhhh, worked with your example on the pics, worked fine...

How did you create the measures value1, value2 and differene

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.