Forum Discussion
morgtd30
Helper I
Helper IGetting correct row level grand totals with switch statement
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 for...
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/547907Awesome!
Here's my first measure:Forecast Amount4 =VAR selectedrow =SELECTEDVALUE(opportunities[msdyn_forecastcategory])RETURNswitch(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])RETURNIF(HASONEVALUE(opportunities[msdyn_forecastcategory]),[Forecast Amount4],SUMX(__table,[__value]))
morgtd30Helper I
Helper IActually, 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]))