divide one column by other

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

[v-yulgu-msft]

Hi @kan,

Is [Total] field in above screenshot an original field in dataset or a calculated column/measure? Please share us source data in dataset.

Regards,

Yuliana Gu

[kan]

Hi

@v-yulgu-msftThanks 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..

[v-yulgu-msft]

Hi @kan,

The wrong results might be caused by summarized data in visualization. 

Please try this measure:

%Complete =
DIVIDE ( SUM ( Node[completed] ), SUM ( Node[Total] ) )

Best regards,

Yuliana Gu

[Greg_Deckler]

Strange, are you sure you are creating a calculated column? I did this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKzcnMzcxLLKpU8MtPSVXwyU/OVtJRMjc3MARSRqZmhkqxOtFKnnnFBanJJZn5eQrO+bkFOaklqQhVJoZmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Nodemajormilestonename = _t, Total = _t, completed = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nodemajormilestonename", type text}, {"Total", Int64.Type}, {"completed", Int64.Type}})
in
    #"Changed Type"

And created this calculated column in that table and got the right answers:

%Complete = DIVIDE([completed],[Total])