Forum Discussion
Resolver IPower Pivot - Forecast Subtotal not totaling future values
Hi DAXRichArd ,
Believe that the issue now is regarding the paraentheses but need to check the formula.
You can share it trough a onedrive, google drive, we transfer link, if there is sensitive information share it trough private message. Or can you make a mockupfile smaller.
Regards,
MFelix
Big thank you. It worked. I hacked at adding SUMX but I didn't know what I was doing. I'm still trying to figure out why your solution works, but I think the bigger picture for me is that it's something to do with context transition and I'm still learning. No matter. I'll keep learning. Big thank you again. You rock! for everyone esle, below is the solution provided to me by MFelix :
Original formula (problem was it did not subtotal the forecasted numbers).
Helper Yr 1 IAH 1% :=
VAR IAHYr1OnePct =
CALCULATE (
Pax[Pax] * 1.01,
Pax[CAMPUS] = "IAH",
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
CALCULATE (
IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),
Pax[CAMPUS] = "IAH"
)
SOLUTION:
Helper Yr 1 IAH 1% :=
SUMX (
'Calendar',
VAR IAHYr1OnePct =
CALCULATE (
Pax[Pax] * 1.01,
Pax[CAMPUS] = "IAH",
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
CALCULATE (
IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),
Pax[CAMPUS] = "IAH"
)
)
Super UserHi DAXRichArd ,
Although not having the file with sample information when using this type of formulas you need to add a SUMX for the subtotals something similar to this:
Helper Yr 1 IAH 1% :=
VAR IAHYr1OnePct =
CALCULATE (
Pax[Pax] * 1.01,
Pax[CAMPUS] = "IAH",
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
IF (
HASONEVALUE ( Calendar[Date].[Month] ),
CALCULATE (
IF ( ISBLANK ( Pax[Pax] ), IAHYr1OnePct, Pax[Pax] ),
Pax[CAMPUS] = "IAH",
SUMX ( Calendar, IAHYr1OnePct )
)
)
Be aware that this may not result as expected since I made it by heart and not having a model if you can share a sample model I can adjust the measure in order to get the information matching your model.
But the mais point here is that the calculations are based on context and when you look at the subtotals it's picking up only the values where you have the pax and the rest is considered as BLANK so not summed in your total.
Regards.
MFelix
- DAXRichArd
MFelix ,
Thanks for your reply.
The response I'm getting is:
"Semantic Error: A function 'SUMX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Do you have a means for me to share my file with you? It's 1.3MB in Excel 2016.
- MFelix
Hi DAXRichArd ,
Believe that the issue now is regarding the paraentheses but need to check the formula.
You can share it trough a onedrive, google drive, we transfer link, if there is sensitive information share it trough private message. Or can you make a mockupfile smaller.
Regards,
MFelix
- DAXRichArd
Thx for your continued support. I sent you a private message with a link in Google Doc with the file. My office contact number is in the message as well.
Thx