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
kan
Helper I
Helper I

divide one column by other

 

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])

 

 

NodemajormilestonenameTotalcompleted%CompleteCorrect Values
Preliminary Node Lock7701256137.561055233.25
Final Node Lock7701186132.9809542 
ER Approval7743182242.0270005 
Node Engineering7701176836.9119816 
Equipment Ordered770159917.5948997 
Make Ready Start770130014.5904787 
Construction Permit Request770181222.3153618 
Make Ready Complete770121912.3539329 
Construction Permit Received770141614.9592466 
Construction Start774362017.9762811 
Power Complete774348211.040731 
Construction Complete774353812.1115371 
Inspection Complete770141611.45646925.4
Integration Complete - Internal77013807.46115686 
Node On-Air – Customer77012645.80146265 
Closeout Complete77114359.91702557 

 

 

Thanks..

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

 

ProjectIdregionstateNodemajormilestonenamenodemajormilestonedefaultsidTotalcompleted
NC-IA-801GRAND-VZWCIAPreliminary Node Lock110
NC-IA-801GRAND-VZWCIAFinal Node Lock210
NC-IA-801GRAND-VZWCIAER Approval310
NC-IA-801GRAND-VZWCIANode Engineering410
NC-IA-801GRAND-VZWCIAEquipment Ordered510
NC-IA-801GRAND-VZWCIAMake Ready Start610
NC-IA-801GRAND-VZWCIAConstruction Permit Request710
NC-IA-801GRAND-VZWCIAMake Ready Complete810
NC-IA-801GRAND-VZWCIAConstruction Permit Received910
NC-IA-801GRAND-VZWCIAConstruction Start1010
NC-IA-801GRAND-VZWCIAPower Complete1110
NC-IA-801GRAND-VZWCIAConstruction Complete1210
NC-IA-801GRAND-VZWCIAInspection Complete1310
NC-IA-801GRAND-VZWCIAIntegration Complete - Internal1410
NC-IA-801GRAND-VZWCIANode On-Air – Customer1510
NC-IA-801GRAND-VZWCIACloseout Complete1610
NC-IA-CLARKEF1-VZWCIAPreliminary Node Lock150
NC-IA-CLARKEF1-VZWCIAFinal Node Lock250
NC-IA-CLARKEF1-VZWCIAER Approval350
NC-IA-CLARKEF1-VZWCIANode Engineering450
NC-IA-CLARKEF1-VZWCIAEquipment Ordered550
NC-IA-CLARKEF1-VZWCIAMake Ready Start650
NC-IA-CLARKEF1-VZWCIAConstruction Permit Request750
NC-IA-CLARKEF1-VZWCIAMake Ready Complete850
NC-IA-CLARKEF1-VZWCIAConstruction Permit Received950
NC-IA-CLARKEF1-VZWCIAConstruction Start1050
NC-IA-CLARKEF1-VZWCIAPower Complete1150
NC-IA-CLARKEF1-VZWCIAConstruction Complete1250
NC-IA-CLARKEF1-VZWCIAInspection Complete1350
NC-IA-CLARKEF1-VZWCIAIntegration Complete - Internal1450
NC-IA-CLARKEF1-VZWCIANode On-Air – Customer1550
NC-IA-CLARKEF1-VZWCIACloseout Complete165

 

 

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.

 

NodemajormilestonenameTotalcompletedComplete
Preliminary Node Lock7701256137.56106
Final Node Lock7701186533.98095
ER Approval7743182242.027
Node Engineering7701176836.91198
Equipment Ordered770159917.5949
Make Ready Start770130014.59048
Construction Permit Request770181222.31536
Make Ready Complete770122313.35393
Construction Permit Received770141715.20925
Construction Start774362017.97628
Power Complete774348211.04073
Construction Complete774353812.11154
Inspection Complete770141611.45647
Integration Complete - Internal77013807.461157
Node On-Air – Customer77012645.801463
Closeout Complete77114359.917026

 

Thanks..

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.