'What If' Analysis Techniques For Power BI

[RAdams]

I'm having some issues tackling a What If scenario. It's more of a two part issue, but I'll start with the first problem. 

I have Sales Data information for the year. Each invoice above $200 is considered a Commissionable Sale. 

I would like a What if Selector (which I 've already created) to calculate each sale and if it above the Threshold What If Rate, then sum it. Like in the table below. 

I've created a Measure similar to this: 

Whatif Commission Sales = 
VAR Sales = IF (SUMX(Sales Table, Sales Table (Sales Amount) > [What If Rate], 1,0)

RETURN
CALCULATE(
SUMX(Sales Table, Sales Table (SalesAmount),
FILTER ( Sales Table, Sales = 1))

But I'm not getting the results I am expecting. Any advice would be appreciated. 

[dedelman_clng]

On the Modeling tab there is a button "New Parameter" which should do what you are looking for.  You specify the name and range of the parameter (numbers only) and it creates a calculated table and measure. It will even create the slicer and put it on the page for you.  You can then reference this in your other DAX.

VAR __WhatIf = SELECTEDVALUE(WhatIf)
VAR Sales = IF (SUMX(Sales Table, Sales Table (Sales Amount)) > __WhatIf, 1,0)
RETURN
CALCULATE(
SUMX(Sales Table, Sales Table (SalesAmount),
FILTER ( Sales Table, Sales = 1))

Hope this helps

David

[RAdams]

@dedelman_clng I did do exactly this but it just doesn't seem to be working for me, or at least in the Table that I'm placing it in. Or am I wrong in assuming that this will not work inside a matrix table? 

[RAdams]

@dedelman_clng So I'm looking closer at your code and I see the SelectedValue there. Why did you put that there and how does it work? I think I'm missing that part. 

R

[RAdams]

Here's the measure I created. Is there something wrong with this:

Whatif Commisionable Sales = 
VAR Threshold = SELECTEDVALUE('Sales Threshold'[Sales Threshold])
VAR Sales = IF ( SUMX('Commission Data','Commission Data'[Sales + Discount]) > Threshold, 1,0)
RETURN 
    CALCULATE(
        SUMX('Commission Data', 'Commission Data'[Sales + Discount]),
    FILTER ( 'Commission Data', Sales = 1)
    )

[dedelman_clng · Accepted solution]

Hi @RAdams

I took your sample data from the original post and made the following code (SalesTab is your data, WhatIf_Amount is the "What If" parameter). Your measure appears to be trying to assign a temporary value (1,0) for each column based on a condition, which is generally not best practice.  You should filter the data with the condition, then run the remaining DAX.

Commissionable Sales =
VAR __WhatIf =
    SELECTEDVALUE ( WhatIf_Amount[WhatIf_Amount] )
VAR __ComAmt =
    CALCULATE (
        SUMX (
            FILTER ( SalesTab, SalesTab[Sale Amount] > __WhatIf ), #Perform SUMX only on rows that satisfy this condition.
            SalesTab[Sale Amount]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( SalesTab, SalesTab[Sale Amount] > __WhatIf ) ) = 0, #To put a 0 instead of BLANK
        0,
        __ComAmt
    )

Note that changing "WhatIf_Amount" changes both tables on the fly.

Hope this helps,

David

[RAdams]

You 'da man @dedelman_clng! Works as planned! 

Learned so much in this one thread. Thanks a billion! 

R

[RAdams]

@dedelman_clng 

Thanks again for your help on this. I've moved on to the second part of my project and I'm stuck. I'm hoping you can give me a pointer on where to start next. I was given some advice but I'm not sure if that's the correct approach. Here is what I have so far: 

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

[dedelman_clng]

Hi @RAdams - 

First you have to understand that PowerBI is not Excel.  The "total" row that you see is really calculating the measure in that specific filter context, not summing everything above it like a pivot table in Excel.  So as you have it currently programmed, the MAXX value of Commission Rate is 10%, the "What if Commissionable GM" totals to 51,174 and 10% * 51,174 is 5,117.4.

So questions I would have -

1) What are the row indicators to the left that you have chosen not to show us? That may make a difference if they are at all related to your measure code.

2) Do you have multiple "What If" variables in a single table? Unless you have all of the combinations populating that table, you should divide up your different variables into their own tables.

3) Do you need to show the What If Commission Rate at the summary level? (I hope not because it would just confuse the reader)

4) Probably others as I dug further into it

You are likely going to have to provide sample data, a data model, any other relevant code and desired outcome(s), if not a full pbix file to get any further assistance from the forums.  I've seen your other thread on this same topic, and anyone replying there is going to want much more detail than you have provided.

[ndonahue]

You could do this by creating a custom column to act as a flag and then utilizign a measure

First create a  custom column (not a measure) to flag all commisionable sales, this will allow you to utilize this many different ways in your data model

Commisionable Sales Flag= if('sales'[salesamount]>=200, 1,0)

Then you can use that in your measure to only sum commisionable sales in a measure:

Commisionable Sales= calculate(sum('sales'[salesamount]), [commisionable sales flag]=1)

Let me know if thats what youre looking for!

[RAdams]

That's pretty much what I'm doing, however, it's the 200 that I'm hoping to replace with the What If Parameter.

I tried to put the What If Value in place where the 200 is in your formula but it's not working.