Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

 

 

Anonymous
Not applicable

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors