Rework IF Measure with Multiple Measures to Total

[paulsnet1986]

I have a table for reporting various financials per customer. One column is an IF measure that essentially says use the formula for a generic calculation for this product column, unless the customer in question is Customer A, B, or C - in that case, use a customer-specific measure.

This works great, but unfortunately is unable to be totaled. Can someone help me rewrite the measure in a way that can be totaled?

Summary Table - Product X Bill Armount =

IF(SELECTEDVALUE('Customer Table'[Customer Name]) = "Customer A", [Customer A - Product X Bill Formula],

IF(SELECTEDVALUE('Customer Table'[Customer Name]) = "Customer B", [Customer B - Product X Bill Formula],

IF(SELECTEDVALUE('Customer Table'[Customer Name]) = "Customer C", [Customer C - Product X Bill Formula],

[Product X Generic Bill Formula]))

[v-rongtiep-msft · Accepted solution]

Hi @paulsnet1986 ,

Please have a try.

Create a measure.

Summary Table - Product X Bill Armount =
VAR _1 =
    SUMX (
        FILTER (
            ALL ( 'Customer Table' ),
            'Customer Table'[date] >= SELECTEDVALUE ( 'Customer Table'[date] )
                && 'Customer Table'[Customer Name] = "Customer A"
        ),
        [Customer A - Product X Bill Formula]
    )
VAR _2 =
    SUMX (
        FILTER (
            ALL ( 'Customer Table' ),
            'Customer Table'[date] >= SELECTEDVALUE ( 'Customer Table'[date] )
                && 'Customer Table'[Customer Name] = "Customer B"
        ),
        [Customer B - Product X Bill Formula]
    )
VAR _3 =
    SUMX (
        FILTER (
            ALL ( 'Customer Table' ),
            'Customer Table'[date] >= SELECTEDVALUE ( 'Customer Table'[date] )
                && 'Customer Table'[Customer Name] = "Customer C"
        ),
        [Customer C - Product X Bill Formula]
    )
RETURN
    measure
        = IF (
            SELECTEDVALUE ( 'Customer Table'[Customer Name] ) = "Customer A",
            _1,
            IF (
                SELECTEDVALUE ( 'Customer Table'[Customer Name] ) = "Customer B",
                _2,
                IF (
                    SELECTEDVALUE ( 'Customer Table'[Customer Name] ) = "Customer C",
                    _3,
                    [Product X Generic Bill Formula]
                )
            )
        )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@paulsnet1986 First, I would recommend switching to SWITCH( TRUE(), ... ) instead of nested ifs. For the total...

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8