Using Sumx and Switch in a measure to provide a total in a table

[SCNCKS1]

Hi,

I am attempting to create a measure that will provide a total when added to a table in a report. The result is working correctly in the table; however, I am not getting a total. I am using the measure to provide a weighted percent complete and it is returning infinity. 

 

Est Qty =

SUMX(

    FILTER(

        PROD_COST_TABLE,

        SELECTEDVALUE(PROD_COST_TABLE[Production_Unit_ID])),

        Switch(

            TRUE(),

            Not(ISBLANK(PROD_COST_TABLE[DA_EST_OH_TOTAL])), PROD_COST_TABLE[DA_EST_OH_TOTAL],

                Not(ISBLANK(PROD_COST_TABLE[DA_EST_UG_TOTAL])), PROD_COST_TABLE[DA_EST_UG_TOTAL],

                    Not(ISBLANK(PROD_COST_TABLE[DA_EST_UNITS_TOTAL])), PROD_COST_TABLE[DA_EST_UNITS_TOTAL],

                        NOT(ISBLANK(PROD_COST_TABLE[DA_EST_FIBERS_TOTAL])), PROD_COST_TABLE[DA_EST_FIBERS_TOTAL],

                0

                )

            )

LS Weighted % Complete =

VAR BID_REVENUE = SUM(PROD_COST_TABLE[LS BID REVENUE])

VAR TOTAL_BID_REVENUE = CALCULATE(SUM(PROD_COST_TABLE[LS BID REVENUE]),ALLSELECTED(TBL_Units))

VAR LS_WEIGHT = BID_REVENUE / TOTAL_BID_REVENUE

 VAR EST_QTY = [Est Qty]

 VAR QTY_Complete = [Qty Complete]

 VAR Percent_Complete = [Qty Complete]/[Est Qty]

RETURN

LS_WEIGHT * Percent_Complete

[AmiraBedh · Accepted solution]

You're calculating a weighted percentage complete, which should be a summation of each row's weighted percentage to give an overall weighted percentage at the end. When you put this measure in a table visualization, it correctly gives you the weighted percentage complete for each row, but when you expect Power BI to provide a total (grand total row) for this measure, it doesn't just sum up the weighted percentages, which would be wrong anyway. Instead, it runs the formula you've provided for the entire table as a context, which may not produce the expected result.

To get the expected grand total for `LS Weighted % Complete`, you'll need a measure that explicitly calculates the weighted average over the entire table when used in a total context. This would involve using `SUMX` again over your table to aggregate row-level calculations.

One way to handle this would be to check if the measure is being evaluated in a total context, and if so, compute the grand total; otherwise, compute the row-level value.

Here's how you can modify the `LS Weighted % Complete` measure to get the expected total:

LS Weighted % Complete =
VAR BID_REVENUE = SUM(PROD_COST_TABLE[LS BID REVENUE])
VAR TOTAL_BID_REVENUE = CALCULATE(SUM(PROD_COST_TABLE[LS BID REVENUE]),ALLSELECTED(TBL_Units))
VAR LS_WEIGHT = BID_REVENUE / TOTAL_BID_REVENUE
VAR EST_QTY = [Est Qty]
VAR QTY_Complete = [Qty Complete]
VAR Percent_Complete = DIVIDE(QTY_Complete, EST_QTY, 0)
VAR RowLevelValue = LS_WEIGHT * Percent_Complete

RETURN
IF(
ISINSCOPE(PROD_COST_TABLE[YourPrimaryKeyColumn]), // Replace "YourPrimaryKeyColumn" with the primary key or a unique column in your table.
RowLevelValue,
SUMX(
ALLSELECTED(PROD_COST_TABLE),
[LS Weighted % Complete]
)
)

[SCNCKS1]

I created calculated columns in the table, added all of the columns together, and did a sum, which provided the correct totals.