Make scenario with two slicers

[Tariq-Algh]

Hello there,
I wish to all good health.
I am aiming to answer this question? what will be my profit look like if branch A. sales drop 25%?
I have many branches (branch A. - branch B. branch C.)
I would like to make a scenario in one branch. (made slicer with branch) i choose the branch.
I made scenario scale. (-100%, -75%, -50%, -25% and -10%) (made slicer with scenario scale).
Kindly help me how can i make such as these scenarios?
And thanks

[Anonymous · Accepted solution]

Hi @Tariq-Algh ,

1.My sample data is this.

BranchesCostSales

branch A	1	4
branch B	2	5
branch C	3	6

2.Create a Branch table by entering data.

3.Create a measure.

Profit =
SUM ( 'Table'[Sales] )
    * ( 1 + SELECTEDVALUE ( 'Branch'[Sales Drop] ) )
    - SUM ( 'Table'[Cost] )

4.The result is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@v-yuta-msft 

Sorry, I may be being a bit clumsy here but how does the "accepted solution" fulfil this part of the brief?

"go to branch A. (SLICER branch A. chosen)
after that go scenario (SLICER SCENARIO SCALE -25% chosen)
then make the calculation (take profit branch A. multiple scenario scale).
sorry i did not mention that i need to know the group level which is branch A. plus branch B. plus branch C."

[PaulDBrown]

@Tariq-Algh 

Here is one way of doing this.

For the example I'm using a simple model with a fact table and a dimension table ('Channel Dim').

I have duplicated the 'Channel Dim' table (called 'Disconnected Dim Channel') and created a new table (called 'Disconnected Sales Drop') with the % sales drop values (25%, 50%, 75%, 100%): neither of these tables has a relationship with the fact table, so they are "disconnected". These are the tables to be used as slicers.

The model looks like this:

The following solution works for single or multiple "Channel" selection
Create the following measures:

1) to calculate the sales amount ([Sum of Actuals] in my example), create a measure which checks the "channel" values selected and reduces the sales by the "sales drop value" selected:

Sales for Selected Channels = 
VAR dimchan = VALUES('Channel Dim'[Channel])
VAR DisconChan = VALUES('Disconnected  Dim Channel'[Disc. Channel])
VAR SELECTactuals = CALCULATE([Sum of Actuals], INTERSECT(dimchan, DisconChan))
RETURN
CALCULATE(SELECTactuals * (1- SELECTEDVALUE('Disconnected Sales Drop'[Sales Drop])))

2)) Finally create the measure to return the correct total sum of the newly calculated sales by channel. The VAR parts calculate the sales for the channels not selected; then just add the [Sales for selected channels] measure:

New Sales = 
VAR dimchan = VALUES('Channel Dim'[Channel])
VAR DisconChan = VALUES('Disconnected  Dim Channel'[Disc. Channel])
VAR OTHERactuals = CALCULATE([Sum of Actuals], EXCEPT(dimchan, DisconChan))
RETURN
OTHERactuals + [Sales for Selected Channels]

And you get this (the channel column in the final tables is from the original DIM Channel table):

[amitchandak]

@Tariq-Algh , Think this needs segmentation /bucket. You need have table with % changes bucket with min-max limit

and you need to create a measure using changes % (Which is the measure too)

Check these if they can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...

if not - Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Tariq-Algh]

Thanks for your fast reply.
I think it’s a a little complex.
i am looking for a simple solution.
what i am thinking is (IN DAX)
go to branch A. (SLICER branch A. chosen)
after that go scenario (SLICER SCENARIO SCALE -25% chosen)
then make the calculation (take profit branch A. multiple scenario scale).
sorry i did not mention that i need to know the group level which is branch A. plus branch B. plus branch C.
so, if i choose branch A. to make it under simulation the branch B. and branch C. their values will not change.

[AllisonKennedy]

@Tariq-Algh
I think this post is very similar to what you are trying to do: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if
You will just need to add in a filter to make sure the B and C values stay constant, so either supply sample data as suggested by others, or try using ALLSELECTED or other filter expressions in DAX.

[Fowmy]

@Tariq-Algh 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn