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

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