cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cristianml Member
Member

Change base of comparison by using slicer selection

Hi,

 

I would like to do the following:  I have 3 different reports but all have same format table : 

 

1 - Actuals

2 - Forecast Version 1

3 - Forecast Version 2

 

I would like to compare "Actuals" against each of the forecast version but in same visualization. Is possible to do this by using slicer ? 

Keep in mind that I have different MEASURES created based on a relation between "actual" and forecast: per example I have a variance cost ( by category) calculated only against one version

Variance Cost = CALCULATE([Actual Costs]-[Forecast Cost])
 
Any Ideas?
 
Thanks !
 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Change base of comparison by using slicer selection

Hi @cristianmi,

A slicer is created based on a column however since your forecasts are rom different columns / tables you cannot create a slicer that refers to all of them at once. That's we're the Forecast Base table is used, this tables allows to have values to place in a slicer to select what measures are used.

In your case this tables must contain one value per each forecast you want to use, it will have 4 lines.

Regarding the measure switch you need to do add as many parameters as forecast you have.
Looking at the formula SWITCH what it does is see what is the selected value on the slicer based on the Forecast Base ta lê and then calculate the corresponding forecast value.

On the question about the forecast are on different tables there is no problem in that, I'm assuming all those tables are related somehow (date prpbably?).

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Change base of comparison by using slicer selection

Hi @cristianml,
In the switch measure you need to have two parameter per option so your measure would look something like this

Forecast Cost =

var Plan = [Forecast Plan Cost]
var Q1 = [Forecast Q1 Cost]
var Q2 = [Forecast Q2 Cost]
var Q3 = [Forecast Q3 Cost]
var Q4 = [Forecast Q4 Cost]
return

IF(DISTINCTCOUNT('Forecast Base'[Base Forecast]) = 1, SWITCH('Forecast Base'[Base Forecast Selected],"Q1", [Q1], "Q2", [Q2] ,"Q3", [Q3] ,[Q4] ),"Select One Forecast for comparisson")

Regards
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




11 REPLIES 11
Super User
Super User

Re: Change base of comparison by using slicer selection

Hi @cristianml ,

 

If you create a table using the forecast comparision something like this:

 

Forecast Base

Forecast1
Forecast2

 

 

Then add the following measures:

ForecastBase = SELECTEDVALUE('Forecast Base'[Forecast Base])


Actuals vs Base =
IF (
    DISTINCTCOUNT ( 'Forecast Base'[Forecast Base] ) = 1;
    SWITCH ( [ForecastBase]; "Forecast1"; [Vs Forecast 1]; [Vs Forecast 2] );
    "Select one forecast for base"
)

Change the [vs Forecast 1] and [Vs Forecast 2] by your measures.

 

Final result below:

 

 

select_forecast.gif

Check PBIX file attach.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




cristianml Member
Member

Re: Change base of comparison by using slicer selection

Hi,

 

What I would need is once I select the Forecast version from Slicer is that the dashboard table can show me only one Forecast column with the Version of the Forecast I selected. Is possible ?

 

Thanks 

Super User
Super User

Re: Change base of comparison by using slicer selection

Hi @cristianml ,

 

Create the following measures:

 

ForecastBase = SELECTEDVALUE('Forecast Base'[Forecast Base])


Actuals vs Base =
IF (
    DISTINCTCOUNT ( 'Forecast Base'[Forecast Base] ) = 1;
    SWITCH ( [ForecastBase]; "Forecast1"; [Forecast 1]; [Forecast 2] );
    "Select one forecast for base"
)

====================================
Forecast 1 = Sum(Table[Forecast1])
====================================
Forecast 2 = Sum(Table[Forecast2])

 

Regards

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




cristianml Member
Member

Re: Change base of comparison by using slicer selection

Hi MFelix,

 

Sorry but I didn't get it. Could you send the attachment ?

 

Thanks,

 

 

Super User
Super User

Re: Change base of comparison by using slicer selection

Hi @cristianml ,

 

See file attach, I just created the new mesasure and add it to the file, check the Base Forecast and Vs Base forecast measures.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




cristianml Member
Member

Re: Change base of comparison by using slicer selection

Hi @MFelix,

 

Thanks for this but not understand the part of Forecast base. I seems these 2 (Forecast1 and Forecast2) are linked to the table and these is used for the Slicer to swich. I have measures (Forecast versions) comes from different queries/Tables. I'd like to replicate the same but not sure how.

other thing, is possible to add more than 2 Versions of forecast ? 

F BASE.jpg

Thanks,

 

Super User
Super User

Re: Change base of comparison by using slicer selection

Hi @cristianmi,

A slicer is created based on a column however since your forecasts are rom different columns / tables you cannot create a slicer that refers to all of them at once. That's we're the Forecast Base table is used, this tables allows to have values to place in a slicer to select what measures are used.

In your case this tables must contain one value per each forecast you want to use, it will have 4 lines.

Regarding the measure switch you need to do add as many parameters as forecast you have.
Looking at the formula SWITCH what it does is see what is the selected value on the slicer based on the Forecast Base ta lê and then calculate the corresponding forecast value.

On the question about the forecast are on different tables there is no problem in that, I'm assuming all those tables are related somehow (date prpbably?).

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




cristianml Member
Member

Re: Change base of comparison by using slicer selection

Hi @MFelix,

 

Thanks for your comments. I was thinking about what you mention regarding Forecast Base that needs to be in One table to allow me to use Slicer. What it occurred to me is that I can make a simple sum/calculation for  each column amount from each different forecast query and locate them in 1 new table with measure.  In this case, should be possible ?

 

Thanks,

 

Regards.

cristianml Member
Member

Re: Change base of comparison by using slicer selection

Hi @MFelix ,

 

Could you please help me ? I tried to replicate the formula but not sure where is the mistake :

 

Forecast Cost =

var Plan = [Forecast Plan Cost]
var Q1 = [Forecast Q1 Cost]
var Q2 = [Forecast Q2 Cost]
var Q3 = [Forecast Q3 Cost]
var Q4 = [Forecast Q4 Cost]
return

IF(DISTINCTCOUNT('Forecast Base'[Base Forecast]) = 1, SWITCH('Forecast Base'[Base Forecast Selected],Plan,Q1,Q2,Q3,Q4),"Select One Forecast for comparisson")
 
base.jpg