'What If' Commission Help

[RAdams]

 @dedelman_clng was such a huge help on my last issue but now I'm having issues with the next step of my project. 

I'm trying to create a Report that will show what will happen when we change the Minimum Sales Threshold and/or the three different Market Segment Percentage Rates in What If Sliders. 

The MarketSegment and Rate Variable are grabbing the Market Segment Percentage from the What If tables. 

The SalesTreshold is grabbing from the Sales Threshold Whatif Table. 

The SalesRate is doing all the calculations on rows where the SalesThreshold is met. 

I'm trying to return all the SalesRate * MarketSegment Rate. 

Whatif Commisionabe GM2 = 
VAR MarketSegment =
    LASTNONBLANK (
        'Commission Data'[Market Segment Filter],
        [Market Segment Filter]
    )
VAR Rate =
    (
        SWITCH (
            MarketSegment,
            "CONTRACTS", MAXX (
                VALUES ( 'Commission Rate Contract'[Contract Commission Rate] ),
                [Contract Commission Rate]
            ),
            "DISTRIBUTOR", MAXX (
                VALUES ( 'Commission Rate Distributor'[Distributor Commission Rate] ),
                [Distributor Commission Rate]
            ),
            "WHOLESALE", MAXX ( VALUES ( 'Commission Rate Wholesale'[Wholesale] ), [Wholesale] )
        )
    )
VAR Threshold =
    SELECTEDVALUE ( 'Sales Threshold'[Sales Threshold] )

VAR SalesRate =
    CALCULATE (
        SUMX (
            FILTER ( 'Commission Data', 'Commission Data'[Sales + Discount] > Threshold ),
            ( 'Commission Data'[Sales + Discount] + 'Commission Data'[Originalcredits]
                + 'Commission Data'[CreditsPriorPeriod]
                + 'Commission Data'[CreditsNotAssociated]
                + 'Commission Data'[Cost]
                + 'Commission Data'[CreditCostPriorPeriod]
                + 'Commission Data'[CreditCostNotAssociated]
                - 'Commission Data'[Commissionable Rebate $] ) 
        )
    )

RETURN
    IF (
        COUNTROWS (
            FILTER ( 'Commission Data', 'Commission Data'[Sales + Discount] > Threshold )
        )
            = 0,
        0,
        ( SalesRate * Rate )
    )

So everything works pretty well by row, but it's not aggregating correctly in my table. The table sums up to 5117.41 but the number should be 4435.09! 

Any help on what I've done wrong would be much appreciated! 

R

[Greg_Deckler]

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

[RAdams]

Thanks for your reply @Greg_Deckler. 

Turning off the Total Row isn't much of an option so I'm going to try the HasOneFilter function. Where do you recommend that I put it? 

R

[Greg_Deckler]

Did you look at the Quick Measure? That should be your guide for how to accomplish what you are trying to do.

[RAdams]

I've been looking at it for 30 minutes now! And to be honest, I'm not sure if I need to Summarize every Variable in my measure or just the HasOneValue Function in the Return Statement. But I'm going to try the easiest thing first and go from there! I'll report back soon. 

R

[RAdams]

Umm... no. I'm kinda lost on how to handle the Summarize and/or the HasOneValue. Can you help me walk through my measure on where I need to focus? 

Thanks!

R