The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am new! I am probably missing something obvious!!! But after 12 hours of trying to figure this out I need help.
Newest version Power BI Desktop. Just want to Divide two numeric columns. When I do the MATH is failing. I have tried this every way I can find, no joy!
The specific math in this example for row 1:
2867 / 3159 = 0.90756568 (using a calculator)
As I understand it, this should be a row by row calculation for this view.
CountTest6 = DIVIDE('YoY_Daily_Sales'[Transaction_Count],'YoY_Daily_Sales'[PriorYear.Transaction_Count])
The answer provided by this formula is 13.53. This is incorrect!
Line | Transaction_Count | PriorYear.Transaction_Count | CountTest6 |
1 | 2867 | 3159 | 13.53084674 |
2 | 1472 | 1732 | 7.749660014 |
3 | 2515 | 2523 | 11.04036185 |
4 | 147 | 180 | 0.816666667 |
5 | 481 | 493 | 3.889987179 |
6 | 1173 | 1291 | 6.29352048 |
7 | 391 | 407 | 1.893128079 |
Solved! Go to Solution.
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..
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..
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?
You are welcome.
@JohnLap,
Your DAX formula is correct. Do you use the latest version of Power BI Desktop(2.53.4954.621)? Which table does the Line field come from? Is it also a field in the YoY_Daily_Sales table?
Regards,
Lydia
Thank you for the response.
I think I may have figured out what is happening, but I am surprised if it is designed this way.
The Line# comes from the same table. It is actually STATE but I changed it to Line # for simplicity.
While the table view in PowerBI is summarized at the Line# level (Group By) there are multiple rows behind that. It appears that Power BI is doing the division on each line of the lowest level of detail and then averaging or summing those. This is absolutely the wrong way to do it. I would have assumed that "Row by Row" calculations meant each Row that is in the VIEW. To solve the problem, I had to create a SQL view in the source table that summarized the data by Line#(State). So, that becomes the lowest level of detail.
Either the documentation, or this DIVIDE function needs to be changed. Users of these reports can easily do the division on the view they see and determine that the math is just wrong. Great way to lose credibility of a report. Also, dramatically reduces the "POWER" in PowerBI if I have to create views to aggregate to every group by level I would want in the report. My expectation was that I could produce the data at the lowest level and have all of the math work out as I grouped as I pleased in Power BI. NOPE!
I see you recreated the problem to test. THANK YOU for taking the time to do that.However, you only recreated the single line of data for each line in the summarized view. If you had multiple rows behind each of the Line# rows, the formula provides the incorrect result. This is the issue.
@JohnLap- can you share the sample data please in excel/PBI - whatever you prefer?
This Posting was "deleted" and marked as success. I reopened it.
I am posting more evidence. The problem is real.
I had to recreate the problem since I resolved it by making summary views in SQL source.
Here is the summary data. This is the actual view created in PBI and the data is below it. I used the native Group By Line#
In the data table I have included the PURE CALC to show the correct answer. The formula provided in Power BI does not work as it should.
Line# | Transaction Count | PriorYear.Transaction_Count | Pure Calc: ((B/C)-1)*100 | PBI CountTest 8 = (DIVIDE('YoYDailySalesbyStateComp'[Transaction_Count],'YoYDailySalesbyStateComp'[PriorYear.Transaction_Count],0)-1)*100 |
1 | 9013 | 7403 | 21.75 | 35.04 |
2 | 4529 | 4610 | -1.76 | 0.28 |
3 | 7641 | 6530 | 17.01 | 22.77 |
4 | 362 | 336 | 7.74 | 51.83 |
5 | 1342 | 1296 | 3.55 | 3.46 |
6 | 3435 | 3632 | -5.42 | -5.37 |
7 | 1116 | 1045 | 6.79 | 18.90 |
8 | 652 | 645 | 1.09 | 1.46 |
Here is the detail lines that make up each of the Line #'s above.
State | Transaction Count | PriorYear.Transaction_Count |
1 | 2370 | 1260 |
1 | 3021 | 3159 |
1 | 3622 | 2984 |
2 | 1289 | 1116 |
2 | 1472 | 1732 |
2 | 1768 | 1762 |
3 | 2242 | 1431 |
3 | 2515 | 2523 |
3 | 2884 | 2576 |
4 | 59 | 23 |
4 | 156 | 133 |
4 | 147 | 180 |
5 | 352 | 353 |
5 | 509 | 450 |
5 | 481 | 493 |
6 | 976 | 1020 |
6 | 1173 | 1291 |
6 | 1286 | 1321 |
7 | 270 | 164 |
7 | 391 | 407 |
7 | 455 | 474 |
8 | 200 | 203 |
8 | 248 | 211 |
8 | 204 | 231 |
Okay this is what I did
let Source = Web.Page(Web.Contents("http://community.powerbi.com/t5/Desktop/ON-ADD-COLUMN-DIVIDE-function-with-two-columns-yields-wrong/m-p/330494#M147703")), Data2 = Source{2}[Data], #"Promoted Headers" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State", Int64.Type}, {"Transaction Count", Int64.Type}, {"PriorYear.Transaction_Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"Transaction Count", each List.Sum([Transaction Count]), type number}, {"Prior Year.Transaction Count", each List.Sum([PriorYear.Transaction_Count]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each (([Transaction Count]/[Prior Year.Transaction Count])-1)*100), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}), #"Rounded Off" = Table.TransformColumns(#"Changed Type1",{{"Custom", each Number.Round(_, 2), type number}}) in #"Rounded Off"
is
I will go apply those steps now and see what I get.
Thank you very much for the support!!!
I will report back after lunch.
No. The formulas work fine at the lowest level of detail record for the source data. In other words, if it is not a summary of the source data then the formula works. If it is summarized, in this example, at the Line # level it does not.
Hi @JohnLap,
I am having same issue where division is going wrong as my data is also summarized.Can you please share how did u solve this in SQL view.
Thanks..
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
94 | |
89 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |