Forum Discussion
ON ADD COLUMN: DIVIDE() function with two columns yields wrong result?
- 8 years ago
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.
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_Mathur8 years ago
Super User
Hi,
There is no mistake in your formula. Share the link from where i can download your PBI fle.
- Anonymous8 years agoNot applicable
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..
- Ashish_Mathur8 years ago
Super User
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?