Forum Discussion
ON ADD COLUMN: DIVIDE() function with two columns yields wrong result?
I am new! I am probably missing something obvious!!! But after 12 hours of trying to figure this out I need help.
Newest version Power BI Desktop. Just want to Divide two numeric columns. When I do the MATH is failing. I have tried this every way I can find, no joy!
The specific math in this example for row 1:
2867 / 3159 = 0.90756568 (using a calculator)
As I understand it, this should be a row by row calculation for this view.
CountTest6 = DIVIDE('YoY_Daily_Sales'[Transaction_Count],'YoY_Daily_Sales'[PriorYear.Transaction_Count])
The answer provided by this formula is 13.53. This is incorrect!
| Line | Transaction_Count | PriorYear.Transaction_Count | CountTest6 |
| 1 | 2867 | 3159 | 13.53084674 |
| 2 | 1472 | 1732 | 7.749660014 |
| 3 | 2515 | 2523 | 11.04036185 |
| 4 | 147 | 180 | 0.816666667 |
| 5 | 481 | 493 | 3.889987179 |
| 6 | 1173 | 1291 | 6.29352048 |
| 7 | 391 | 407 | 1.893128079 |
Correction: The real solution was a little more complex
Transaction Var % Measure = (DIVIDE(CALCULATE(SUM('Table'[Count])),CALCULATE(SUM('Table'[PriorYear.Count])),0)-1)*100
This actually solves the problem.
Converting to a measure is required and will not work against a Direct Query.
19 Replies
JohnLap,
Your DAX formula is correct. Do you use the latest version of Power BI Desktop(2.53.4954.621)? Which table does the Line field come from? Is it also a field in the YoY_Daily_Sales table?
Regards,
LydiaThank you for the response.
I think I may have figured out what is happening, but I am surprised if it is designed this way.
The Line# comes from the same table. It is actually STATE but I changed it to Line # for simplicity.
While the table view in PowerBI is summarized at the Line# level (Group By) there are multiple rows behind that. It appears that Power BI is doing the division on each line of the lowest level of detail and then averaging or summing those. This is absolutely the wrong way to do it. I would have assumed that "Row by Row" calculations meant each Row that is in the VIEW. To solve the problem, I had to create a SQL view in the source table that summarized the data by Line#(State). So, that becomes the lowest level of detail.
Either the documentation, or this DIVIDE function needs to be changed. Users of these reports can easily do the division on the view they see and determine that the math is just wrong. Great way to lose credibility of a report. Also, dramatically reduces the "POWER" in PowerBI if I have to create views to aggregate to every group by level I would want in the report. My expectation was that I could produce the data at the lowest level and have all of the math work out as I grouped as I pleased in Power BI. NOPE!
smpa01Community Champion
I see you recreated the problem to test. THANK YOU for taking the time to do that.However, you only recreated the single line of data for each line in the summarized view. If you had multiple rows behind each of the Line# rows, the formula provides the incorrect result. This is the issue.
Hi All,
I am new to power bi and am trying to divide two existing columns and get result into calculated column,but the results are incorrect.can someone please help.I tried to use below formula
%Complete = DIVIDE(Node[completed],Node[Total])
Nodemajormilestonename Total completed %Complete Correct Values Preliminary Node Lock 7701 2561 37.5610552 33.25 Final Node Lock 7701 1861 32.9809542 ER Approval 7743 1822 42.0270005 Node Engineering 7701 1768 36.9119816 Equipment Ordered 7701 599 17.5948997 Make Ready Start 7701 300 14.5904787 Construction Permit Request 7701 812 22.3153618 Make Ready Complete 7701 219 12.3539329 Construction Permit Received 7701 416 14.9592466 Construction Start 7743 620 17.9762811 Power Complete 7743 482 11.040731 Construction Complete 7743 538 12.1115371 Inspection Complete 7701 416 11.4564692 5.4 Integration Complete - Internal 7701 380 7.46115686 Node On-Air – Customer 7701 264 5.80146265 Closeout Complete 7711 435 9.91702557 Thanks..
- Ashish_Mathur
Super User
Hi,
There is no mistake in your formula. Share the link from where i can download your PBI fle.
Thanks for the reply.Following is my source code for dataset.Both Total and Completed are original fields and part of dataset and are grouped by node name.I am struck when trying to calculate completed/total in power bi.
Source Code:
select ProjectId,region,state,milestonename as Nodemajormilestonename,nodemajormilestonedefaultsid,Total,completed
from (
select distinct p.WspNetworkId as projectid,p.regionid as region,p.networkstate as state,milestonename,nodemajormilestonedefaultsid,count(distinct m.projectnodeid) Total,
sum(case when actualmilestonedate is not null then 1 else 0 end) as completed
from dbo.ProjectMaster p
join
(
select
ProjectdesignId
,NodeName
,max(ProjectNodeId) ProjectNodeId
from dbo.ProjectNode
group by
ProjectDesignId
,NodeName
) n on (p.ProjectDesignId = n.ProjectDesignId)
join dbo.ProjectNodeMajorMilestone m on (n.ProjectNodeId = m.ProjectNodeId)
group by MilestoneName,p.WspNetworkId,p.regionid,p.networkstate,nodemajormilestonedefaultsid)x
order by projectid,nodemajormilestonedefaultsidFollowing is my Source dataset:
ProjectId region state Nodemajormilestonename nodemajormilestonedefaultsid Total completed NC-IA-801GRAND-VZW C IA Preliminary Node Lock 1 1 0 NC-IA-801GRAND-VZW C IA Final Node Lock 2 1 0 NC-IA-801GRAND-VZW C IA ER Approval 3 1 0 NC-IA-801GRAND-VZW C IA Node Engineering 4 1 0 NC-IA-801GRAND-VZW C IA Equipment Ordered 5 1 0 NC-IA-801GRAND-VZW C IA Make Ready Start 6 1 0 NC-IA-801GRAND-VZW C IA Construction Permit Request 7 1 0 NC-IA-801GRAND-VZW C IA Make Ready Complete 8 1 0 NC-IA-801GRAND-VZW C IA Construction Permit Received 9 1 0 NC-IA-801GRAND-VZW C IA Construction Start 10 1 0 NC-IA-801GRAND-VZW C IA Power Complete 11 1 0 NC-IA-801GRAND-VZW C IA Construction Complete 12 1 0 NC-IA-801GRAND-VZW C IA Inspection Complete 13 1 0 NC-IA-801GRAND-VZW C IA Integration Complete - Internal 14 1 0 NC-IA-801GRAND-VZW C IA Node On-Air – Customer 15 1 0 NC-IA-801GRAND-VZW C IA Closeout Complete 16 1 0 NC-IA-CLARKEF1-VZW C IA Preliminary Node Lock 1 5 0 NC-IA-CLARKEF1-VZW C IA Final Node Lock 2 5 0 NC-IA-CLARKEF1-VZW C IA ER Approval 3 5 0 NC-IA-CLARKEF1-VZW C IA Node Engineering 4 5 0 NC-IA-CLARKEF1-VZW C IA Equipment Ordered 5 5 0 NC-IA-CLARKEF1-VZW C IA Make Ready Start 6 5 0 NC-IA-CLARKEF1-VZW C IA Construction Permit Request 7 5 0 NC-IA-CLARKEF1-VZW C IA Make Ready Complete 8 5 0 NC-IA-CLARKEF1-VZW C IA Construction Permit Received 9 5 0 NC-IA-CLARKEF1-VZW C IA Construction Start 10 5 0 NC-IA-CLARKEF1-VZW C IA Power Complete 11 5 0 NC-IA-CLARKEF1-VZW C IA Construction Complete 12 5 0 NC-IA-CLARKEF1-VZW C IA Inspection Complete 13 5 0 NC-IA-CLARKEF1-VZW C IA Integration Complete - Internal 14 5 0 NC-IA-CLARKEF1-VZW C IA Node On-Air – Customer 15 5 0 NC-IA-CLARKEF1-VZW C IA Closeout Complete 16 5 0
Below is data tried to build in visualisation mode from datasets.here i used
Complete = DIvide(Query1[completed],Query1[Total] which is giving wrong results (might be data is summarized) ).For example for Preliminary Node Lock complete=2561/7701 =0.332 but is giving 37.56 which is wrong.
Nodemajormilestonename Total completed Complete Preliminary Node Lock 7701 2561 37.56106 Final Node Lock 7701 1865 33.98095 ER Approval 7743 1822 42.027 Node Engineering 7701 1768 36.91198 Equipment Ordered 7701 599 17.5949 Make Ready Start 7701 300 14.59048 Construction Permit Request 7701 812 22.31536 Make Ready Complete 7701 223 13.35393 Construction Permit Received 7701 417 15.20925 Construction Start 7743 620 17.97628 Power Complete 7743 482 11.04073 Construction Complete 7743 538 12.11154 Inspection Complete 7701 416 11.45647 Integration Complete - Internal 7701 380 7.461157 Node On-Air – Customer 7701 264 5.801463 Closeout Complete 7711 435 9.917026 Thanks..
