What if parameter

[ScaR]

I'm new to Power BI. I'm trying to have a card for total sales % and use what if parameter to adjust the quantity sold of only one product to see that impact on the sales. 

I have a slicer for the product and a what if parameter for the quantity. And for the total sales %(measure) on the card not to be changed based on the slicer, I have turned on edit interaction on the slicer and sent the card to none.

I want to see how the sale of a particular product would impact the company's total sales %. Any help is appreciated.

[Greg_Deckler]

OK, it is very difficult to know where to begin with the information provided. Best thing to do is to post some small amount of sample data that serves as a good representation of the actual data, usually just a few rows. Also post what the expected outcome would be from that data. The more information the better. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

With the information provided I can only guess as to how the data is laid out but what I am expecting that you want is that you want to take the total sales of the company minus any sales of the product selected in the slicer and then add in the quantity of sales selected in the What If parameter for the product selected in the slicer. Is this a true statement?

[ScaR]

Thank you for your response Greg.

Here is the scenario. I have a table like this,

Product	Cost	Price	Profit	Qty
A	100	115	15	4
B	50	60	10	5
C	20	25	5	2

And a measure for total profit, which I'm displaying as a card. Then, I have slicer for product and a What if parameter for qty change.

Now, I want to have the Total Profit card to show the total profit of all the products - $120 (per above table), even if, I select the product B on the slicer. Also, if I add qty for product B to 3 (total - 8), I want the total profit to display $150.

[amitchandak]

I am really not able to connect with number $120 and $150. basis of adding 3.

[ScaR]

Here is the break up.

Total Profit(Measure) = Profit per product * Qty

$120 = (15*4) + (10*5) + (5*2)

So, in the what if parameter if I add 3 qty to Product B (meaning if we are able to sell 3 more product B), then total qty for product B is 8.

Total Profit: $150 = (15*4) + (10*5) + (5*2) + (10*3) {this 10* 3 comes from the what if).

[Greg_Deckler · Accepted solution]

@ScaR what do you think of this solution?

Total Profit = 
    VAR __Product = SELECTEDVALUE('Table'[Product],BLANK())
    VAR __NewQty = [QtyValue]
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ALL('Table'),
                "__NewQty",IF('Table'[Product] = __Product,__NewQty,[Qty])
            ),
            "__Total Profit",'Table'[Profit] * [__NewQty]
        )        
RETURN
    SUMX(__Table,[__Total Profit])

Attached PBIX