cancel
Showing results for
Did you mean:

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

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 ID Date Days Current Budget (£k) Forecast Days Cumulative Days Previous Revenue Submitted (£) Cumulative Revenue AAA 01/12/2018 0 10000 1000 0 0 0 AAA 01/06/2018 5 10000 1000 5 100 100 AAA 01/11/2018 10 10000 1000 15 200 300 AAA 01/10/2018 20 10000 1000 35 300 600 AAA 01/09/2018 25 10000 1000 60 400 1000 AAA 01/08/2018 31.5 10000 1000 91.5 500 1500 AAA 01/07/2018 38 10000 1000 129.5 600 2100 AAA 01/05/2018 44.5 10000 1000 174 700 2800 AAA 01/04/2018 51 10000 1000 225 800 3600 AAA 01/03/2018 57.5 10000 1000 282.5 900 4500 AAA 01/02/2018 64 10000 1000 346.5 1000 5500 AAA 01/01/2018 70.5 10000 1000 417 1100 6600 AAA 01/12/2017 77 10000 1000 494 1200 7800 AAA 01/11/2017 83.5 10000 1000 577.5 1300 9100 AAA 01/10/2017 90 10000 1000 667.5 1400 10500 AAA 01/09/2017 96.5 10000 1000 764 1500 12000 AAA 01/08/2017 103 10000 1000 867 1600 13600 AAA 01/07/2017 109.5 10000 1000 976.5 1700 15300
1 ACCEPTED SOLUTION
Helper IV

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.

4 REPLIES 4
New Member

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

Here the table

Here the fields

It's possible to do it?

tks everyone

Frequent Visitor

Have you solved this? I got the same problem.

Helper IV

Anyone cracked it yet? Lol

Helper IV

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.

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors