cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Teker939
Regular 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

@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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

13 REPLIES 13
Teker939
Regular Visitor

@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!

parry2k
Super User
Super User

@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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

@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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

 

 

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.
 

@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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

@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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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

@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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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!



 

@Teker939 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.






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

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





Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!