Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KH11NDR
Helper IV
Helper IV

Subtracting two measures in a matrix table and getting the wrong answers

Hi Guys,

 

When I try this measure below, my results are nowhere near the number I'm looking for. I know it's going to be hard to solve this one with the details given., The tables a matrix and I also have a master calendar
linked to my main fact table.

 

Date as Columns

 

Measures as values in the Matrix tabe.

 

Subtract = [Total to Earn - % Complete of Project] - [Measure Previous month]


Measure Previous month = CALCULATE(SUM(Merge1[Cumulative Revenue]),PARALLELPERIOD(Merge1[Date],-1,MONTH))

 

Total to Earn - % Complete of Project = [Sum of Current Budget] * [% Complete (Total Project)]

 

% Complete (Total Project) = CALCULATE(sum(Merge1[Cumulative Days])) / CALCULATE(sum(Merge1[Forecast Days]))

 

Sum of Current Budget = CALCULATE(SUM(Merge1[Current Budget (£k)]))


% Complete (Total Project) = CALCULATE(sum(Merge1[Cumulative Days])) / CALCULATE(sum(Merge1[Forecast Days]))

 

Here's the table.

 

Project IDDateDaysCurrent Budget (£k)Forecast DaysCumulative DaysPrevious Revenue Submitted (£)Cumulative Revenue
AAA01/12/20180100001000000
AAA01/06/201851000010005100100
AAA01/11/20181010000100015200300
AAA01/10/20182010000100035300600
AAA01/09/201825100001000604001000
AAA01/08/201831.510000100091.55001500
AAA01/07/201838100001000129.56002100
AAA01/05/201844.51000010001747002800
AAA01/04/2018511000010002258003600
AAA01/03/201857.5100001000282.59004500
AAA01/02/201864100001000346.510005500
AAA01/01/201870.510000100041711006600
AAA01/12/20177710000100049412007800
AAA01/11/201783.5100001000577.513009100
AAA01/10/201790100001000667.5140010500
AAA01/09/201796.5100001000764150012000
AAA01/08/2017103100001000867160013600
AAA01/07/2017109.5100001000976.5170015300
1 ACCEPTED SOLUTION

I've sorted it, bit of advice, if your calculations are well off, check the formats of your numbers, one was one decimal place out hence made a huge differnce.  It was general data type in Excel, changed it to Number and made sure all my numbers in the columns and measures were all the same format and boooom the correct answer.

View solution in original post

4 REPLIES 4
guilhermeieda
New Member

Hello everyone.
I have some problem like this post.
However I can't solved.

 

Here the table

 

ERROR.png

Here the fields

ERROR2.png

It's possible to do it?

 

tks everyone

 

@KH11NDR 

 

Have you solved this? I got the same problem.

KH11NDR
Helper IV
Helper IV

Anyone cracked it yet? Lol

I've sorted it, bit of advice, if your calculations are well off, check the formats of your numbers, one was one decimal place out hence made a huge differnce.  It was general data type in Excel, changed it to Number and made sure all my numbers in the columns and measures were all the same format and boooom the correct answer.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.