Forum Discussion

ScaR's avatar
ScaR
Regular Visitor
6 years ago
Solved

What if parameter

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 th...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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

ScaR's avatar
ScaR
Regular Visitor
6 years ago

Thank you for your response Greg.

 

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

 

ProductCostPriceProfitQty
A100115154
B5060105
C202552

 

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's avatar
amitchandak
Super User
6 years ago

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

  • ScaR's avatar
    ScaR
    Regular Visitor
    6 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      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