Hi @Diana1 , (version 3, with 3 scenarios, with more explanation of points 4 data model connections and 8.1 Treatas formula)
I have made a workout for revenue of your interesting question, with the following concept:
-
made three scenario data sets, two by copying the base data âModelâ and the third made by a calculated table, all with independent flexible filtering possibilities on customers, channel, product, period and country to analyse and create scenarios on those new sets.
-
add the differences from the three 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.
See PBIX :
Scenario Analysis Question v21 plus sc4 calc table.pbix (1.2 MB)
Technical workout of the concept:
- Copied the base data set to âSc1 Baseâ and âSc2 Baseâ with Query Editor, and create a calculated table (see âAdditional Infoâ below for further detailed description)
- Added an index to all 4 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 copied new data sets (6 in total), just to be able to sort values of dimensions, (by Sort by Columns e.g. MonthInCalender by MonthnYear), no new lookup tables are created for the calculated table.
- Connected in the Data Model the lookup tables to the two copied Scenario data sets, (not tested if separate lookup tables for calculated table also works)
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).
An very important reason to disconnect the scenarios from the base model in the data model is to avoid synchronization of the filtering of the base model with any of the scenarios.
If for example Customer 8 is selected in Scenario 1, the base model should remain all customers, and not being filtered. If Scenario 1 were connected in the data model, the filtering would synchronize. Each scenario should be Stand-alone in the data model.
- 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 four measure groups, DS Scenario 1, DS Scenario 2, DS Scenario 4 and DS Base Total. The measures in DS Scenario 1, 2 and 4 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 for each Scenario, see measure âSc1 Varianceâ, filtering the scenario selection (see 8.1 below). Treatas formulas are needed as the scenarios have no connection to the base model in the data model. With no connections in the data model, separate filtering is possible in the base model and in each scenario.
-
8.2. a Slicer Synchronization for each slicer used (21 in total) at the three Scenario reporting pages (see below 8.2). Without the Slicer synchronization, the Revenue difference for the Scenarios is not taken (it then follows the granularity of the base reporting page)
8-1 Treatas Measure of Scenario 1
Sc1 variance = // SLICER SYNCHRONIZATION REQUIRED FROM SCENARIO 1 PAGE, TO EACH REPORTING PAGE USING THIS MEASURE
CALCULATE( [Sc1 Diff Rev],
TREATAS(VALUES('Model'[Index]), '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 :
====================================================================
Additional information:
- The copying of the base data set can also be done by creating a calculated table(see scenario 4 in attached PBIX), which might be preferable if the base data set only contains a few columns, is not too big and frequently changes.
Sc1 Base =
ADDCOLUMNS(
SUMMARIZE( 'Model',
'Model'[Index], Dates[MonthInCalendar], 'Model'[Customer Name],'Model'[Country], 'Product'[Product Name], 'Model'[Channel]),
"Base4Price", [Base avg unit price], "Base4Volume", [Base volume])
Please note that this calculated table should be stand alone in the data model (no data connections), to avoid circular references, and âIndexâ is added as a unique key to be able to connect this data to the Model data.
The slicers for this scenario are directly generated from the dimensions generated in the calculated table, like Customer Name, Country, and Channel, see also the Scenario 4 reporting page in the PBIX.
The Volume and Price variation settings are âborrowedâ from Scenario 1 for simplicity, possible better to add two new parameters.
(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 )))
Return S1Revenue
- 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
Revenue with base model , scenario 1, 2 and 4 example
Scenario 4
Basic graph of base plus scenarios revenue
.
