Forum Discussion

morgtd30's avatar
morgtd30
Helper I
3 years ago
Solved

Getting 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 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.
Need Help
  • morgtd30's avatar
    morgtd30
    3 years ago

    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]))
     

1 Reply

  • morgtd30's avatar
    morgtd30
    Helper I
    3 years ago

    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]))