cancel
Showing results 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

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

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors