topic Re: How to calculate a variation of a measure depending on a slicer selection? in DAX Commands and Tips

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

Anonymous — Mon, 22 Jul 2019 00:07:09 GMT

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!

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

parry2k — Mon, 22 Jul 2019 01:13:05 GMT

Anonymous 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.

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

Anonymous — Mon, 22 Jul 2019 01:26:21 GMT

Month	Version	Gross Sales
Jan	Scenario 1	100
Jan	Scenario 2	110
Jan	Scenario 3	130
Jan	Scenario 4	180
Feb	Scenario 1	110
Feb	Scenario 2	130
Feb	Scenario 3	180
Feb	Scenario 4	100

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

parry2k — Mon, 22 Jul 2019 01:37:42 GMT

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

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

Anonymous — Tue, 23 Jul 2019 00:37:27 GMT

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

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

Anonymous — Tue, 23 Jul 2019 01:21:21 GMT

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.

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

parry2k — Tue, 23 Jul 2019 02:51:33 GMT

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

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

Anonymous — Tue, 23 Jul 2019 12:17:31 GMT

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

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

parry2k — Tue, 23 Jul 2019 15:13:46 GMT

Anonymous 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.

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

Anonymous — Sat, 27 Jul 2019 23:43:07 GMT

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

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

parry2k — Sun, 28 Jul 2019 14:17:33 GMT

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

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

Anonymous — Sun, 28 Jul 2019 19:47:51 GMT

parry2k The one you sent lets you compare the two variables, but you cannot have a bar chart, for example comparing the monthly evolution of the two variables, because they are two different measures.

I don´t know if what I´m trying to do is even possible.

Maybe it cannot be done in the same report and I have to have two different reports to show these information cuts.

Thanks!

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

parry2k — Sun, 28 Jul 2019 20:06:25 GMT

Anonymous you can month on x -axis and both those measure on values and you should able to get you are looking for. I'm trying to help without knowing full details.

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

Anonymous — Sun, 28 Jul 2019 23:10:57 GMT

parry2k Thanks! It all worked.

Apologies for the back and forth...it´s hard to explain with words so many variables and I couldn´t find a way to attach the .pbx so you could see my problem.

But it is working now!

So thank you!