ON ADD COLUMN: DIVIDE() function with two columns yields wrong result?

[JohnLap]

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

[kan]

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]

Hi,

There is no mistake in your formula.  Share the link from where i can download your PBI fle.

[kan]

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,nodemajormilestonedefaultsid

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

[Ashish_Mathur]

Hi,

I am not sure of what that codde is doing but i assume that you want to first group by the 4th column and then add the numbers from the last 2 columns.  If that be the case, then create a Table visua and drag the 4th column in the row labels.  Then write these measures:

Total1 = SUM(Data[Total])

Total2 = SUM(Data[Completed])

Total3=[Total2]/[Total1]

Does that help?

[kan]

Hi  @Ashish_Mathur,

   Thanks for the reply.Yaa this worked.

[Ashish_Mathur]

You are welcome.

[v-yuezhe-msft]

@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,
Lydia

[JohnLap]

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

[smpa01]

[JohnLap]

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.

[smpa01]

@JohnLap- can you share the sample data please in excel/PBI - whatever you prefer?

[JohnLap]

This Posting was "deleted" and marked as success.  I reopened it.

I am posting more evidence.  The problem is real.

[JohnLap]

I had to recreate the problem since I resolved it by making summary views in SQL source.

Here is the summary data.  This is the actual view created in PBI and the data is below it.  I used the native Group By Line#

In the data table I have included the PURE CALC to show the correct answer.  The formula provided in Power BI does not work as it should.

Line#	Transaction Count	PriorYear.Transaction_Count	Pure Calc: ((B/C)-1)*100	PBI CountTest 8 = (DIVIDE('YoYDailySalesbyStateComp'[Transaction_Count],'YoYDailySalesbyStateComp'[PriorYear.Transaction_Count],0)-1)*100
1	9013	7403	21.75	35.04
2	4529	4610	-1.76	0.28
3	7641	6530	17.01	22.77
4	362	336	7.74	51.83
5	1342	1296	3.55	3.46
6	3435	3632	-5.42	-5.37
7	1116	1045	6.79	18.90
8	652	645	1.09	1.46

Here is the detail lines that make up each of the Line #'s above.

State	Transaction Count	PriorYear.Transaction_Count
1	2370	1260
1	3021	3159
1	3622	2984
2	1289	1116
2	1472	1732
2	1768	1762
3	2242	1431
3	2515	2523
3	2884	2576
4	59	23
4	156	133
4	147	180
5	352	353
5	509	450
5	481	493
6	976	1020
6	1173	1291
6	1286	1321
7	270	164
7	391	407
7	455	474
8	200	203
8	248	211
8	204	231

[smpa01]

Okay this is what I did

let
    Source = Web.Page(Web.Contents("http://community.powerbi.com/t5/Desktop/ON-ADD-COLUMN-DIVIDE-function-with-two-columns-yields-wrong/m-p/330494#M147703")),
    Data2 = Source{2}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State", Int64.Type}, {"Transaction Count", Int64.Type}, {"PriorYear.Transaction_Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"Transaction Count", each List.Sum([Transaction Count]), type number}, {"Prior Year.Transaction Count", each List.Sum([PriorYear.Transaction_Count]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each (([Transaction Count]/[Prior Year.Transaction Count])-1)*100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type1",{{"Custom", each Number.Round(_, 2), type number}})
in
    #"Rounded Off"

is 

[JohnLap]

I will go apply those steps now and see what I get.

Thank you very much for the support!!!

I will report back after lunch.

[smpa01]

Is this what you are looking for ?

[JohnLap]

No.  The formulas work fine at the lowest level of detail record for the source data.  In other words, if it is not a summary of the source data then the formula works.  If it is summarized, in this example, at the Line # level it does not.

[kan]

Hi  @JohnLap,

             I am having same issue where division is going wrong as my data is also summarized.Can you please share how did u solve this in SQL view.

Thanks..

[JohnLap · Accepted solution]

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.