Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
You need to use only MEASURES for this!
Amt1 Total = SUM('Table'[Amount1]) Amt2 Total = SUM('Table'[Amount2]) Difference = [Amt1 Total] - [Amt2 Total] Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )
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
This is what I have in PBI:
Do you know why the division is not correct?
You need to use only MEASURES for this!
Amt1 Total = SUM('Table'[Amount1]) Amt2 Total = SUM('Table'[Amount2]) Difference = [Amt1 Total] - [Amt2 Total] Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )
I have 2 columns Im using for calcluation: Crashes, TotalInstalls
Using the syntax posted, I get an error:
@Sean wrote:You need to use only MEASURES for this!
Amt1 Total = SUM('Table'[Amount1]) Amt2 Total = SUM('Table'[Amount2]) Difference = [Amt1 Total] - [Amt2 Total] Divide = DIVIDE ( [Difference], [Amt1 Total], 0 )
You will get the values per name in the Table - you have the names on the Rows!
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
So what should be the Value for A?
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!
mhhh, worked with your example on the pics, worked fine...
How did you create the measures value1, value2 and differene
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |