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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.