cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Teker939 Frequent Visitor
Frequent Visitor

How to calculate a variation of a measure depending on a slicer selection?

I have a Database with 2 columns: Scenario & Gross Sales.

I´ve created a Total Gross Sales Measure that is only the sum of those gross sales and I can compare Gross Sales by scenario if I create a bar chart for example.

I created a slicer so I can choose the scenarios that I want to see on the charts/Matrix

Now I want to add a card or a Matrix that shows the Gross sales difference between the selected scenarios on the slicer but that the value changes as I change my selection in the slicer.

I´ve tried different DAX but couldn´t get the result I wanted...and it seems that this should be pretty easy to achieve because it is so basic. 

Thanks so much for the help!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: How to calculate a variation of a measure depending on a slicer selection?

@Teker939 isn't it page 2 in my previous attachment doing that. once scenario is fixed and you compare other scenario against it.






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

13 REPLIES 13
Super User IV
Super User IV

Re: How to calculate a variation of a measure depending on a slicer selection?

@Teker939 you should have seperate table scenario for slicer so that it doesn't filter your main table. Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Teker939 Frequent Visitor
Frequent Visitor

Re: How to calculate a variation of a measure depending on a slicer selection?

MonthVersionGross Sales
JanScenario 1100
JanScenario 2110
JanScenario 3130
JanScenario 4180
FebScenario 1110
FebScenario 2130
FebScenario 3180
FebScenario 4100
Super User IV
Super User IV

Re: How to calculate a variation of a measure depending on a slicer selection?

@Teker939 attached, you can tweak it as per your need. I guess this will get you started.






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Teker939 Frequent Visitor
Frequent Visitor

Re: How to calculate a variation of a measure depending on a slicer selection?

Thanks for your quick answer @parry2k!

I wanted to attached a revised version of the file you kindly shared with me, but I don´t even know how to do that.

 

To your report I added a monthly bar chart. I would basically need the bar chart to always show the Scenario 1 Gross Sales and compare it to whichever the selection is in the slicer.

And a Card also that shows the Diff measure you created but always comparing the Scenario 1 vs. the one that is selected in the slicer.

Is there a way to do that?

Thanks again for your help...I feel we are really close.

 

Best

Martin

 

 

DaxNewbie2019 Frequent Visitor
Frequent Visitor

Re: How to calculate a variation of a measure depending on a slicer selection?

How about creating the measures like below in the .pbix file

 

Scenario1Val = CALCULATE( [Total Gross Sales], 'Table'[Version] = "Scenario 1" )
DiffOfScenario1AndSelectedScenario = [Scenario1Val] - [Total Gross Sales Selected Scenario]
 
 
I am new here and also would like to know how to attach files with the post.
 
Super User IV
Super User IV

Re: How to calculate a variation of a measure depending on a slicer selection?

@Teker939 if the ask is to always compare against scenario 1 then it is much easier. Here is updated file, check page 2






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Teker939 Frequent Visitor
Frequent Visitor

Re: How to calculate a variation of a measure depending on a slicer selection?

Thanks @parry2k. We are getting there. The problem with that solution is that it doesn´t allow me to do a monthly bar chart. Since I have to measures as values it doesn´t allow me to add a legend, right?

 

Thanks!

 

Martin

Super User IV
Super User IV

Re: How to calculate a variation of a measure depending on a slicer selection?

@Teker939 well the solution was based on your original post, if you have other things to achieve, please explain in details and then we can look into it.






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Teker939 Frequent Visitor
Frequent Visitor

Re: How to calculate a variation of a measure depending on a slicer selection?

@parry2k sorry to keep adding things to the request. I´m new to PowerBi so it is not that easy for me to explain what I need. And I try to use what you share with me to get to where I need to go.

I have a lot of information for 4 different scenarios and I would need to be able to compare any two scenarios I want in different monthly charts (Bar & Lines), but at the same time to be able to add tables and cards with % Var between the two selected scenarios in the slicer.

If it is easier to always have a fixed scenario and only change the scenario that I compare the first scenario to...I could live with that.

 

Is there a way to do all of that? I think it is sort of a combinatio of the two solutions that you already shared with me in this post.

 

Again, thanks for helping

Martin

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors