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,
There is no mistake in your formula. Share the link from where i can download your PBI fle.
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_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?
- Anonymous8 years agoNot applicable
- Ashish_Mathur8 years ago
Super User
You are welcome.