cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cristianml
Post Patron
Post Patron

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

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

15 REPLIES 15
MFelix
Super User III
Super User III

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix,

 

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

 

Thanks,

 

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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,

 

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

 

 

  

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Thanks @MFelix  ! 🙂

Hi @MFelix ,

 

How can in Select Excel the Comparison from the SWICH formula I used in PBI ?

 

Thanks,

COMP.png

 

 

Hi @cristianml 

 

Not really sure what you mean, but do you want to create a similar formula on your excel spreadsheet?

 

Can you give little more context.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

What I meant regarding the follwing measure.. When I Export to excel from a visual It doesn't show me the number, what it shows to me (in excel once imported) is ""Select One Forecast for comparisson". How I add this Slicer in the exported excel ?

 

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")

 

 

Thanks,

Regards.

 

Hi @MFelix ,

 

Is possible to add another option in the switch measure once it was already created ?

 

Thanks,

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors