Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ScaR
Regular Visitor

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 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.

1 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

 

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

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).

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.