What if scenario for each item

DAX DAX Calculations
1

Hi guys,

I have building av scenario analysis series on my sales tables just based on demand and pricing. Everything looks good.

I have managed to be able to click on several products to increase/decrease just these products in demand or price. Also used the Scenario = [All sales]-[selectedsales]+[Scenario revenue] , too get the selected sales revenvue and non selected revenue.

BUT - here is mye problem. If I got 8 different products in mye sales table - I want to be able too change pricing and demand scenario for each product. For example:

Scenario.xlsx (8.9 KB)

Sales table.xlsx (1.6 MB)

The attached file is just example, I really want too adjust the slicer settings for each product individually. I want to go on product A, adjust the demand, then product B and adjust the demand differently etc - and the choises stay for each price or demand change. I want to make individually changes to each product with slicers, and then collect all demand changes in to graphical views for each product and then total.

Example:
Adjust product A demand 2%
Adjust product B demand 6%
Adjust product C demand 7%
Adjust product D -10%

I want to see each change pr product after adjusting everyone seperatly.

Thanks!

2

Hi,
Could you share your pbix?
It is all about context so need to see the approach you took.

Paul

Enterprise%20DNA%20Expert%20-%20Small

1 Like
4

This is an interesting scenario and not that simple if you want to create individual adjustments for every single product. It is possible but just quite modeling intensive.

Hereā€™s a technique I would recommend having a look at for this, where you can build a multiple layers of scenarios using a supporting table similar to the below.

image
image.png732Ɨ285 40.9 KB

Yours would obviously be different as you want to focus on your products mainly.

I will keep having a think about this one, but thereā€™s a bit to it I must say, due to the almost unlimited potential for values.

You could just create new slicers for each different product here, but Iā€™m not sure if thatā€™s what you want to do as it would take up a lot of room in your model.

Sam

5

Also have you gone through this course to really understand all the nuances of working with scenario analysis?

Thereā€™s a bit to it, but the setup here will be absolutely crucial to enable you to scale this into multiple selections.

6

Hi,

How can I make different slicers for each product? When I change each slicer, wouldn`t all data change?

A slicer for each product is fine - I could just use one uniqe page for the slicers, and a another one for the tables and graphic. This is going to be both a tracking tool for the weekly status and a ā€œif scenarioā€. So if is possible to make a slicer for each product, and the slicer connected to each product only changes the one product - that is fantastic. How can I do that?

In the perfect world I would want this:

-slicer 1: For product A
-slicer 2: For product B
-slicer 3: For product C
etc

Even better - if its possible: How can i make the slicer for each product only change unique weeks?

For example: sales development for 3 products. In the perfect world, I would want to maybe change the scenario for product A on week 201910-201915 (increase), and decrease product B on week 201918-201921. (decrease)

I want the graphic view to show the exact same weeks as 1-21 all the time, only the values change.

image
image.png915Ɨ449 58 KB

7

Hi paul,

Gave Sam a answer below. Can you please also take a look? :slight_smile:

Thank you!

8

Hi,

Of course I can use ā€œedit interactionsā€ and make several slicers.

But, how kan I use the slicers to only increase certain weeks?

9

Thereā€™s is quite a bit to getting this all to work. And itā€™s just not that easy thatā€™s why I mentioned the above technique as an alternative to look at.

Iā€™ll try to give you some advice below without having to basically complete the whole development as an answer here.

First, you will have to create different slicers for each product you want to be able to run scenarios on.

You can do this quite quickly with parameters

image

Then from here, you will need to break out each different product into new DAX formulas which reference the slicers you have on your page.

This means you will have 7 (in your example) formulas, ie. Product 1 Sales, Product 2 Salesā€¦

The DAX formula will have to be something like the below (from the recent learning summit model)

Product 2 Sales = 
CALCULATE(
    SUMX( Sales,
        ( ( Sales[Order Quantity] * ( 1 + [Store Demand Value] ) ) *
            ( Sales[Unit Price] * ( 1 + [Discounting % Value] ) ) ) ),
                Products[Product Name] = "Product 2" )

image
image842Ɨ678 40.3 KB

Then your total scenario sales, will be the addition of all these unique DAX formulas for each product.

Now the specific time issue. Basically impossible (hesitate to say this but it potentially is, Iā€™ll have to think about it more over time) to complete this is a dynamic way in Power BI. Mainly because you would need 7 unique date tables that allow you to manipulate certain time frame selectively for easy scenario on each product. Overall I donā€™t like how this would all play out in a model. Way to many tables.

You can do this statically by creating a table of data, which shows between specific start dates and end dates that you want to increase/decrease sales by a certain percentage.

You would have columns including.

Product Name
Start Date
End Date
% Change

You would then need to use the ā€˜events in progressā€™ pattern to bring this into your calculations

See below for information on how to complete this. Itā€™s basically the technique that allows you to manage parameters between dates in Power BI.

Also I go into this technique a bit in this workshop here (bit different to your scenario here but the technique is exactly the same)

So in conclusion, the exact this youā€™re looking for here isnā€™t possible in itā€™s purest sense. You can work around it and get close to what you need, but not the total dynamic package youā€™re likely looking for.

Sam

10

Just remembered this is actually a better example of how to work around specific times and is something I came up with a few months ago.

This is a whole workshop which details how to set it up and then how to run the calculations required. Remember though this isnā€™t the dynamic way you were thinking itā€™s more a static way.