Getting correct row level grand totals with switch statement

[morgtd30]

I have a requirement where we are displaying a forecast pipeline, and I'm using multiple what-if parameters to allow users to set the percentage weight of each category and then add up to a total forcasted amount.

I'm current using this formula in a matrix table and the column subtotals add up correctly based on the user-selected parameters, but I get blank on the grand total column:

Forecast Amount4 =

VAR selectedrow =

SELECTEDVALUE(opportunities[msdyn_forecastcategory])

RETURN

sumx(opportunities,

switch(

selectedrow,

100000001,[Total Amount]*ProspPara[ProspPara Value],

100000004,[Total Amount]*LowPara[LowPara Value],

100000002,[Total Amount]*ModPara[ModPara Value],

100000007,[Total Amount]*ConfPara[ConfPara Value],

100000003,[Total Amount]*CommitPara[CommitPara Value],

100000005,[Total Amount]*WonPara[WonPara Value]

))

How can I create a measure that will calculate the total sum using the context (category) of each row? If it needs to be in an adjacent table, so be it.

[morgtd30 · Accepted solution]

Actually, I figured this out using this article. He wasn't kidding on The Final Word.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Awesome!

Here's my first measure:

Forecast Amount4 =

VAR selectedrow =

SELECTEDVALUE(opportunities[msdyn_forecastcategory])

RETURN

switch(

selectedrow,

100000001,[Total Amount]*ProspPara[ProspPara Value],

100000004,[Total Amount]*LowPara[LowPara Value],

100000002,[Total Amount]*ModPara[ModPara Value],

100000007,[Total Amount]*ConfPara[ConfPara Value],

100000003,[Total Amount]*CommitPara[CommitPara Value],

100000005,[Total Amount]*WonPara[WonPara Value],0

)

Second measure:

Forecast Amount5 =

VAR __table = SUMMARIZE(opportunities,opportunities[msdyn_forecastcategory],"__value",[Forecast Amount4])

RETURN

IF(HASONEVALUE(opportunities[msdyn_forecastcategory]),[Forecast Amount4],SUMX(__table,[__value]))