Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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])
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

        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