Thanks to everyone who has ben participating in #WorkoutWednesday – Power BI edition. This week we are going to be diving into switching metrics in your reports using SWITCH() and a slicer. This method can be especially useful when designing reports for mobile use. Toggling between metrics allows you to save real estate on your report and allow users to focus on one metric at a time. See the example report below:
- Create a report view that allows users to toggle between the metrics Total Revenues, Total Expenses, and Total Profits.
- Create a Measures Table that lets you reference these values using the SWITCH() function, which you must use.
- Use a horizontal slicer to allow you to toggle between the metrics
- Make sure users aren’t able to see all three metrics at once – we want users to be able to focus on one metrics at a time.
- Formatting is totally up to you! We’ve been loving the creativity that the #WOW2021 community has been producing – keep it coming!
This week uses a data set that breaks down NCAA athletic department expenses and revenues by year. You can get it here at data.world. This view is a summarized view that is similar to data used in the first few challenges, but it’s not the same.
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
7 thoughts on “2021 Week 8 | Power BI: SWITCH()ing Measures”
Why doesn’t Current Year = YEAR(TODAY()) work for CY and PY calculation?
becuase the latest data in the dataset is 2017. it should be selected measure’s year not today’s year
I am not able to change the values between expenses revenue and profits can you guide me through
This was great, lots of stuff packed in here!
One thing for the conditional color: instead of always picking the color based on rules (which could get really old with a lot of choices or where you have to do it a lot of places), you can create a measure to do it in one place. Makes it way easier to tweak the colors later, too.
1, “#0D6ABF”, //Total Revenues color
2, “#EB895F”, //Total Expenses color
3, “#006B13” //Total Profits color
[Note that you still have to do the legend hack for the bar and area, since the tables aren’t connected. If they were connected, you could use this for those, too.]
Thanks for this comment – very interesting.
I got the colour changes with your method for the horizontal bar chart but not the line chart.
I guess the recently released Field Parameters takes care of a lot of this though I guess you still need these techniques to get the colours and other changes linked to that slicer.
This Was amazing content.