Introduction
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:
Requirements
- 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!
Dataset
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.
Share
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.
Why doesn’t Current Year = YEAR(TODAY()) work for CY and PY calculation?
hello,
I am not able to change the values between expenses revenue and profits can you guide me through
Excellent!
becuase the latest data in the dataset is 2017. it should be selected measure’s year not today’s year
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.
Color =
SWITCH(SELECTEDVALUE(MeasuresTable[Order]),
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.]
This Was amazing content.
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.
No data available in the link provided
Hi Aditya – You can find the data in that link, you need to navigate to the “NCAA Profit and Losses Summary” and you do have to be logged into Data.World. Try this link – does that work?
Solution for this challenge https://youtu.be/WxFlE1Kcybk?si=f81CZYPwRpn_WmiU
Hello , these challenges are great for beginners, Thank you !!
I am unable to set up color change for Area chart, I also tried putting measures in the Legend field . I was able to change for Bar chart . Can anyone tell me solution for Area chart ?
Hi Shannon! Awesome challenge for beginners but the color for the legend is overriding my visual. i am unable to assign a preferred color to the visual as it takes the color of the legend and even if i apply it it doesnt change.