Hi @Diana1 ,
I have made a workout for revenue of your interesting question, with the following concept:
- copy the base data “Model” to two scenario data sets, and flexible filter on customers, channel, product, period and country to analyse and create scenarios on those new sets.
- add the differences from the two scenario sets, as compared to the base set, back into the base dataset for further analysis and overview.
It is an indirect solution, with the advantages that overview remains and very dynamic scenario analysis is possible. This workout might be an option for different kinds of information, as available in the base data model, like gross margin, there are a lot of combinations possible.
Scenario Analysis Question v20.pbix (1.1 MB)
Technical workout of the concept:
- Copied the base data set to “Sc1 Base” and “Sc2 Base” with Query Editor (see Add.Info below for an alternative)
- Added an index to all three data sets, to be used as an unique key (The key “Index” is the lowest possible granularity, all granularity levels will be reported)
- Copied or created new Lookup tables Dates, Product, Customer for both new data sets (6 in total), just to be able to sort values of dimensions, (by Sort by Columns e.g. MonthInCalender by MonthnYear)
- Connected in the Data Model the lookup tables to the new two Scenario data sets, DISCONNECTED ALL OTHER automatic newly generated connections in the data model, to avoid data connections of the new data sets with the existing data model. (ambiguity/circular references)
- Added four parameters for price and volume mutations for the two scenarios via Modeling–> Parameter
- Renamed all the dimensions used in the slicers used in the scenarios (e.g. “1Country”) to show that the slicers are from different dimensions.
- Created are three measure groups, DS Scenario 1, DS Scenario 2 and DS Base Total. The measures in DS Scenario 1 and 2 are: Base revenue, Scenario Revenue (see Add Info) and the difference.
- Revenue differences, based upon the scenarios, are reported together with the base model in two steps:
8-1. a Treatas filter measure “Sc1 Variance”, filtering the scenario selection (see below1)
8-2. a Slicer Synchronization for each slicer used (14 in total) at the two Scenario pages with each reporting page (see below2). Without the Slicer synchronization, the Revenue difference for the Scenarios is not taken (it then follows the granularity of the reporting page)
8-1 Treatas Measure of Scenario 1
Sc1 variance = // SLICER SYNCHRONIZATION REQUIRED FROM SCENARIO 1 PAGE, TO REPORTING PAGES USING THIS MEASURE
Var Sc1ScenTDiffRevenue = CALCULATE( [Sc1 Diff Rev],
TREATAS(VALUES('Model'[Index]), 'Sc1 Base'[Index]))
FILTER( 'Sc1 Base', 'Sc1 Base'[Index]))
8-2 Slicer Synchronization
Each slicer, used in the two Scenario pages (14 in total), needs to be synchronized with the base reporting pages, to let the Treatas filter measure as stated above, work, see above for one slicer setting (View → Sync Slicers);
Ticking both boxes makes the slicers Synchronized AND Visible :
- The copying of the base data set can also be done by creating a calculating table, which might be preferable if the base data set only contains a few columns, is not too big and frequently changes.
Sc1 Base =
'Model'[Index], Dates[MonthInCalendar], 'Model'[Customer Name],'Model'[Country], 'Product'[Product Name], 'Model'[Channel]),
"Base1Price", [Base avg unit price], "Base1Volume", [Base volume])
(Issue might be that sorting like periods, products etc. is not (easy) possible, as lookup table sorting with calculated tables is not straight forward, it gives e.g. april 2018, april 2019)
- The scenario revenue is calculated, after the standard filtering on dimensions period, customer, products, country and channel, as follows:
Sc1 Scen Revenue =
//SLICER VALUES SCENARIO 1
Var Price1Ch = SELECTEDVALUE( 'Sc1 PriceChange'[Sc1 Price1Change],0) // pricechange scenario 1, default = 0
Var Volume1Ch = SELECTEDVALUE('Sc1 VolumeChange'[Sc1 Volume1Change], 0) //volumechange scenario 1, default 0
var S1Revenue = SUMX( 'Sc1 Base', ( 'Sc1 Base'[Volume] * (1 + Volume1Ch )) * ('Sc1 Base'[Unit Price $] * (1 + Price1Ch )))
- Practice tip creating new slicers, copying from existing slicers: make sure before copying or changing, that the Synchronized Tick box is UNTICKED, to avoid strange slicer reactions from the synchronized (invisible) slicer(s) on the new page or on another reporting page(s).
Kind regards, JW
Reports made in this workout: see also PBIX
Base model + scenario 1 and 2 revenue example
Base graph scenarios