Column in a Matrix visual with Switch formula not giving a total

[Rena]

I created this measure to calculate accounts that are above a spend threshold or return a blank value

var SelectedItem = SELECTEDVALUE('Actual Spend'[Account])

RETURN

SWITCH (TRUE(),

SELECTEDITEM = "1 DIR LA HOURLY FRINGE", " ",

SELECTEDITEM = "1 DIR LA HOURLY WAGES", " ",

SELECTEDITEM = "1 DIR LA LESS:  INTERNAL ORDERS", " ",

SELECTEDITEM = "2 MIX OH CONTRACT SERVICES" && [Spend % of Total]>.05, sum('Actual Spend'[Actual])-(0.05*[Total Spend]),

SELECTEDITEM = "2 MIX OH CONTRACT SERVICES" && [Spend % of Total]<=.05, " ",

SELECTEDITEM = "2 MIX OH INDIRECT HOURLY WAGES", " ",

SELECTEDITEM = "2 MIX OH SHUTTLE", " ",

SELECTEDITEM = "2 MIX OH SUPPLIES" && [Spend % of Total]>.06, sum('Actual Spend'[Actual])-(0.06*[Total Spend]),

SELECTEDITEM = "2 MIX OH SUPPLIES" && [Spend % of Total]<=0.06, " ",

SELECTEDITEM = "2 MIX OH UTILITIES", " ",

SELECTEDITEM = "3 FIX OH DEPRECIATION", " ",

SELECTEDITEM = "3 FIX OH EMPLOYEE RELATIONS" && [Spend % of Total]>.008, sum('Actual Spend'[Actual])-(0.008*[Total Spend]),

SELECTEDITEM = "3 FIX OH EMPLOYEE RELATIONS" && [Spend % of Total]<=.008, " ",

SELECTEDITEM = "3 FIX OH INDIRECT HOURLY FRINGE", " ",

SELECTEDITEM = "3 FIX OH INSURANCE/TAX/LICENSES", " ",

SELECTEDITEM = "3 FIX OH MAINTENANCE & REPAIR", " ",

SELECTEDITEM = "3 FIX OH OTHER" && sum('Actual Spend'[Actual])>0, sum('Actual Spend'[Actual]),

SELECTEDITEM = "3 FIX OH OTHER" && SUM('Actual Spend'[Actual])<=0, " ",

SELECTEDITEM = "3 FIX OH RENT/LEASE", " ",

SELECTEDITEM = "3 FIX OH SALARIED FRINGE", " ",

SELECTEDITEM = "3 FIX OH SALARIED LABOR", " ",

SELECTEDITEM = "3 FIX OH SEASONAL OFFSET", " ",

SELECTEDITEM = "3 FIX OH TRAVEL & ENTERTAINMENT" && [Spend % of Total]>.001, sum('Actual Spend'[Actual])-(0.001*[Total Spend]),

SELECTEDITEM = "3 FIX OH TRAVEL & ENTERTAINMENT" && [Spend % of Total]<=.001, " ")

The formula works fine and returns exactly what I want.  However, there is no totals on the Spend Benchmark Difference column.

[Rena]

Still not totaling.  Thanks for the blank tip - I'm pretty new to PowerBI and hadn't heard of that one yet.  I even switched out the blank for 0 and it still didn't put a total column.  Also tried creating a new visual in case I had somehow done something to my current one.  Verified total is on for all columns and still no total...

[AlexisOlson · Accepted solution]

The SUMX part you need. I think I may have mixed up row context and filter context effects.

Try using just the row context:

SUMX (
    DISTINCT ( 'Actual Spend'[Account] ),
    VAR SelectedItem = 'Actual Spend'[Account]
    RETURN
        SWITCH (
            TRUE (),
            SELECTEDITEM = "1 DIR LA HOURLY FRINGE", BLANK(),
            SELECTEDITEM = "1 DIR LA HOURLY WAGES", BLANK(),
            SELECTEDITEM = "1 DIR LA LESS:  INTERNAL ORDERS", BLANK(),
            [...et cetera...]
        )
)

[Rena]

That worked.  I never in a million years would have known or stumbled on that.  Thank you!

[AlexisOlson]

At the total level, SELECTEDVALUE returns a blank because there is no single account. Thus the measure returns a blank in this filter context.

To sum across acounts, you need to actually do a sum. Try wrapping the whole thing with SUMX

SUMX (
    DISTINCT ( 'Actual Spend'[Account] ),
    VAR SelectedItem = SELECTEDVALUE ( 'Actual Spend'[Account] )
    RETURN
        SWITCH (
            TRUE (),
            SELECTEDITEM = "1 DIR LA HOURLY FRINGE", BLANK(),
            SELECTEDITEM = "1 DIR LA HOURLY WAGES", BLANK(),
            SELECTEDITEM = "1 DIR LA LESS:  INTERNAL ORDERS", BLANK(),
            [...et cetera...]
        )
)

(Note: I'm using blanks instead of " ".)