Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
I am struggling with a measure total. I've been through the solutions online but they don't seem to work (obviously, my application of these solutions is not correct). I have a lead forecast by month, as below. When actual leads come in (see the second column) this adjusts the forecast to a 'new estimate'. That all works fine, except the total of the new estimate is wrong, since it is only adding up part of the calculated result.
The leads are first added together from two tables:
Everything else is in a table called 'LEAD FORECAST':
ShortMonthYear
Leads Forecast
The new forecast is a simple IF:
The total of this column is wrong. It only adds up 'Actual Leads' since at the total level, leads do not equal zero. This is my attempted fix:
This corrects the error in the original formula by adding in the estimates to the total - but it then excludes the Actual Leads. So it produces the exact opposite result and is still wrong.
If I try to add the 'Actual Leads' to the total, it adds just 158 instead of 14,560:
I don't know why. But this next 'fix' re-creates the 158 on the individual lines, without adding it to the total:
So - what do I need to do to get a total of 98,264?
Thank you
Thank you. That didn't work but the concept did - it seems I was trying to add up the two columns too early, so it wasn't getting the right value row-by-row.
Hey GE1,
Try this:
SUMX (
'LEAD FORECAST',
COALESCE ( 'LEAD FORECAST'[Leads Forecast], 'LEAD FORECAST'[Actual Leads] )
)
The key is to go row by row and get the value, then sum all the values of the rows together at the total level. Let me know if that's not what you were looking for. (From looking at your post, it looks like ShortMonthYear, Actuals Leads and Leads Forecast are all columns in the same table.)
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |