Welcome to our webinar. I'm Andrej of Zebra BI, and today I'll present our 19th Zebra BI webinar, where we are exploring the world of reporting and dashboarding in Power BI and in Excel. This time, I'll present my top five favorite DAX tricks for building dashboards in Power BI.

Watch video recording and
download PBIX examples

In the previous webinars, many of you have asked me, "Oh, how did you do that trick? How do you do the year-to-date slicer? Can you change the measures dynamically in charts?" Tricks like that actually enhance the usability and the user-friendliness of your dashboards in a really massive way.

I have seen a lot of dashboards in Power BI, and the thing is, it's a fantastic product, and you can do a lot with it. I've seen so many colorful and beautiful dashboards that tend to have a few problems. First of all, sometimes they're just simply not effective enough. Secondly, they tend to get really complicated when you want to build something.

People are using bookmarks, they are hiding charts and putting lots of elements on the page, which is really complicated to build and even more complicated to maintain. That's why I've decided to share a few Power BI DAX modeling best practices and tricks which will hopefully speed up the development of your dashboard pages and reports and make them much easier to maintain in the future.

My agenda for today, quite simple. I'll show you my top five tricks. This webinar will be a little more technical and I will definitely try to build a practical sample of a dashboard in 40 to 45 minutes. Hopefully, I make it and I'll explain some of the DAX formulas along the way. I will focus especially how to use DAX and how to use these pieces of a DAX model in Power BI in a smart way to enhance your dashboards.

After the webinar, we will share all the resources and the download links. Our chief technology officer, Mitja from Zebra BI, is here with us and will help you in the chat. If you have any kind of question, you can just go ahead and type your questions and while I'm doing the presentation, Mitja will just try to follow up with you over the chat, send you all the links and so on. Otherwise, I'll just make a wrap up at the end of the webinar where we'll also answer your questions.

If there's anything unanswered, we'll do a Q&A at the end, so just type your questions into your questions box. We will send you the recording, which will be available tomorrow or the day after tomorrow, along with all the PBIX examples that I'm gonna build today and the Power BI DAX code.

Looking around Power BI

I'll just switch to Power BI now and show you a couple of examples that I'm trying to build right now. First of all, we'll start with some basics, like just getting your time series right and getting your calendar right, things like your months, quarters, years, days, weekdays and others. The core element of every Power BI DAX model is actually a good calendar dimension, so we'll just start off with the basics.

Once we have that, we'll gradually start to build things like actual versus previous comparison. We'll calculate the previous year values and do the comparison. For example, here I have my actual value. We are looking at the sales revenue and this is the previous year's sales, and this is my growth from previous year in absolute terms, and this is my growth in previous year in percentages.

You can do a monthly comparison versus previous year, which is a very basic concept but you need to know your DAX to do this in Power BI.

The next thing is the switch. It's a very simple way to switch from monthly to year-to-date results.

If you have a monthly development like this and you compare it to the previous year, it's really not obvious what's going on. You see monthly values, but how does this accumulate to year-to-date values? What's the final result at the end? If you do have a year-to-date switch, you can actually change the view like this so you see the accumulated values. It's a comparison of actuals versus my previous year.

You can also add the slicers that affect the whole dashboard page. I'll show you how to build this in quite an easy way.

I am currently basically working with just two visuals. Everything else is just done in the model itself so you can switch to the year-to-date view. You can also switch your KPI. Right now, this is the revenue and I want to see the whole page for the gross profit. You just need to click on the gross profit and everything will change to show the gross profit. We'll build a few pages that just take advantage of this model. Using the same model it's easy to build different views, monthly views, trends, structured tables and comparisons.

We'll add the comparison to the plan. These are like the three most important categories: previous year, actual and plan. You can build nice variance analysis reports like this one, which are completely dynamic, so you can just dive into the details.

You can also change the order of elements. You can have the previous year, actual and plan, and then you can put the variance in between. You can just shuffle things around and reorder things a little bit. You've built yourself a nice variance report for the month of September.

Starting from scratch

Let me start with a completely empty Power BI file and just try to build at least maybe 70% of what I've shown so far.

Let's start with a completely blank Power BI document. The first step, of course, is just to get some data. I have my data in an Excel spreadsheet, so I'll just connect to my Excel.

I am using my sales demo database. I've got a couple of tables here and here's my sales. This is my sales data - three years worth of sales data from 2016 to 2018. It covers daily sales, so I have a date field here, and then I have some product IDs, customers, sales persons, business units, and so on. I have three major KPIs or measures: the revenue, the costs, and gross profit.

I use an additional field that's in a column called scenario. This indicates whether this is actual data or planned data. It's all joined together in the same table. We call this a fact table. Then I have typical dimensions, like and business units, such as divisions, groups and so on. There is also lots of customers from different regions and countries.

We are also working with products, sales persons, account managers and so on. I'll just load everything into Power BI and we'll start analyzing this, trying to build a model and make sense out of it to gain some insights. We'll prepare a nice dashboard that you'll be able to share with end users and your management. After everything is loaded into my Power BI, I can switch to my data panel here to see all my tables.

Looking at the relationships between tables

The tables include business units, customers, products and so on. Switch to the last tab showing the relationships between tables. This is my model and it should not look too confusing. I still think that the so-called star schema model is the best way to model things in Power BI. It's a best practice that when you load data into Power BI you try to plan for a star schema. This means that you have one big facts table, the so-called fact table, with all the facts and measures. Then you can build all your dimensions around it.

Power BI has already created some relationships here and let's to explore this in Power BI.

I can select the Revenue field and I should also select the Date field. Here it is. I'm doing some sort of analysis with native Power BI visuals. You can see the trends based on the yearly values. You can drill down into this but it's hard to make sense out of this data. You can see that there's sort of a yearly trend and that revenue is improving over time.

But it's really hard to see what is going on. The labels are problematic here, and maybe the best solution would be to just use the line chart. But this is really not an analysis. Once you start building reports dashboards, you have a goal in your mind, especially in monthly reporting. I'll try to build a monthly report. First of all, you need to add a comparison. Once you compare one value with another value you start to understand what is going on: is this better or worse than planned? How much better, how much worse? Is it better than the growth from the previous year?

Once you have the comparison in your model, then you can actually start analyzing things and communicating them. We will need to build the comparisons and everything else. The first thing we need to do is work on the date values. You see that if you just have a normal date field here, you will see the trends and so on, but that's more or less what you can do. We will return to our model and work on the calendar dimension.

Setting up the calendar dimension

Every good model in Power BI starts with the calendar dimension, and we'll switch to the data view to show you how to do this in DAX. We'll start working with some DAX formulas. Just switch to the modeling tab, and we will create a new table. I'll just call it Calendar and while I'm building this, you'll get the idea why this is important. This is my first Power BI DAX function for tonight. Just a calendar. There's a function called CALENDAR which actually builds a list of dates, so that's your first option.

Let's build a calendar from the first of January to 2018 - that's the end of my data - end of 2018. This will return a simple column called Date.

Calendar = CALENDAR("2016-01-01, "2018-12-31")

That's exactly what we already have in our sales table. I'm doing this now so that after I have this column, I can add new columns. I clicked the New column button and now I can add my own time functions to create new columns. The first one we'll just call Year, and we'll just extract the year from the date. That's really simple to do.

So, these are the pure basics for those of you who are more or less beginners in Power BI. The function year will extract this for me, and I'll just refer to my date field here. This simply returns the year, here. So, the YEAR function just returned the year. Next I'll just do this in a format. Use the FORMAT function to format the date in a number of different ways. To get the year you just type the year and four Y letters. This formula returned the year, but this time it's as text.

"Year", FORMAT([Date], "yyyy"),

Now we'll add another column. We'll call it Month. We'll use the FORMAT formula again. Format the date, and this time we'll use three M characters.

"Month", FORMAT([Date],"MMM"),

This is how you can build your custom columns which you will use in your reports. Let's add the quarter, as well. I'll use the format date to extract the quarter and use Q for quarter. This returns a number between one and four. Just a number. If you want to have values like Q1, Q2, Q3, and so on, you can use it. You completely control the labels and how the labels look like.

You should add the letter Q in front of the number and use concatenating. You can just type in the text Q and then join this with the end operator that provides the number. Enter this and you get Q1.

That's the standard way in Power BI. Most of people do this. There's a even easier way; you can simply add the letter Q that you need to escape it. If you do it like this, the backslash displays the letter Q, which is followed by the number of the quarter.

"Quarter", FORMAT([Date],"\QQ"),

There are other functions, like weekday, weeknum for week number, and so on, that you can use to build other columns in your time or date or calendar dimension.

Using CALENDARAUTO

There's another way how to do this. I'll show you an alternative way in DAX, how to do this in Power BI. There's actually a function which can help you. It's called ADDCOLUMNS, which can actually add all the columns within this function. You use the ADDCOLUMNS function to say the calendar will use the CALENDARAUTO function.

Then just add all the columns at once. I'll simply copy the whole formula here. I'll create a new table like I did before, but this time I'll just build everything together. Just paste it inside, and this is the Power BI DAX that I'll share with everybody. This formula has built the whole calendar table with all the columns and you can tweak how do you want your years to look like. You decide whether you'll use four numerals or two numerals? The CALENDARAUTO is a version of the CALENDAR function which automatically searches for the dates in your database and automatically finds the minimum-maximum date and just builds everything automatically.

Calendar = ADDCOLUMNS ( CALENDARAUTO (),
    "Year", FORMAT([Date], "yyyy"), 
    "MonthNo", MONTH([Date]), 
    "Month", FORMAT([Date],"MMM"),
    "Quarter", FORMAT([Date],"\QQ"),
    "YearMonth", FORMAT([Date],"YYYY-MM"), 
    "WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat
    "Weekday", FORMAT([Date],"ddd"), 
    "WeekNo", WEEKNUM([Date], 2),
    "Week", "W" & WEEKNUM([Date], 2) )

This'll work like in 80% of cases. Otherwise, there are other options to build your calendar dimension in Power Query, which is another part of Power BI that I don't intend to cover today, because my title is DAX tricks in Power BI. What have we gained so far? We've got the calendar dimension and instead of using the date field from your sales table you're using the calendar dimension allowing you to be much more flexible. For example, you can now say: "I want to see my sales by year."

However, this is still not working. There is no yearly trend and the entire chart is flat. Why is that? Because we have a new dimension but it's not linked and not related to my data. You need to click here on the Date field and just create a relationship to the sales table. Link the Date field here to the Date field in the Sales table. I will actually hide the Date field in the Sales table, because from now on we will not use it in the report anymore. Instead, we'll just use the calendar dimension. Now this is working but the sort order is not okay.

This is a common questions that we get from people who are starting Power BI: "Why is my sort order not correct?" For the years on every visual you need to switch to your field that you want to sort by, and select Sort Ascending to sort the years correctly.

If you do this for the month, there's another caveat. If you have a monthly view and you sort ascending, you don't get the right sort order. This is just an alphabetical sort order, so you need to tell Power BI how to sort the months. These are things that look completely obvious to every Excel users but these are real, real challenges that people are facing when they start using Power BI.

You can solve this straight from your report view here. You click on your month and then go here to click Sort by column button. Open this to find your month number, which is one column in your calendar dimension. This chart is now sorted by the month number. Now we can start analyzing data.

Switching to Zebra BI for Power BI

We can start building dashboards. To do this, I will first import Zebra BI visuals. At the moment I'll just import two visuals from Zebra BI, which we are developing.

Now we have the revenue by month, and I have other fields and I'll use the year as a slicer here. This allows the end user to choose the year, which is a basic thing in a report. Drag the Year measure onto the canvas and switch to the Slicer visualization and I'll set it as a dropdown menu. This'll be just my first selection here, my first slicer.

So far, this is still quite boring. I can change the year and there's some results here. This is still very basic, just some monthly developments and trends. You just just see basic things like: "Is it better in the summer or better in December?" or something like that. You can see some trends and outliers and things like that, but that's just not enough. We'll start by comparing this to previous year.

Now that we have the calendar dimension, we can actually add another field for the previous year, which will calculate the previous year. On top of the revenue, we'll look at the revenue for the previous year. First, I'll do something else. What we are looking at here is not really our revenue, because I actually have two types of data in my sales database. I have plan data and actual data together jammed into the same table. It's in this column called scenario. So if I just add the scenario here, you'll see the actual data and the plan.

So I need to, first of all, separate actual results from the plan. I will build my first measure here and we'll just click new measure.

We'll call it Revenue AC. This is the actual part of my data, and I'll do this by first running a SUM function on all the values of the revenue column. That would be just a normal measure without any kind of filtering. Now I need to filter it. I will use the formula called CALCULATE. The formula will take the sum of my revenue field and then filter this.

I'll use another formula called FILTER, which will actually filter my sales table. I type in Sales for my sales table, and I'll use the filter expression. I'll use the Scenario column. I will add my condition here and the scenario column should match actual to display my actual revenue here.

Revenue AC filtered = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Scenario]="AC"))

I will not use just the column, but I will use this measure in my chart. I'll throw out the old one, and I'll use the actual values. We are now looking at my actual sales in December of 2017. Now that we have this, we can also create another measure for the previous year.

I'll just take the actual revenue and just shift the year by one to look at the past year. You can do this by using the CALCULATE formula again. Here's the formula:

Revenue PY = CALCULATE([Revenue AC], DATEADD('Calendar'[Date], -1 , YEAR))

This takes my actual revenue, which we just calculated before, and now we'll use the DATEADD function, which is kind of the most versatile and just safest to work with. Otherwise, you can try other ones. There's actually a formula called PREVIOUSYEAR, which is easy to use, but has some caveats. Then you can use the same period last year and the parallel periods and a few others. But I just do this, use DATEADD to take my dates from the calendar.

We'll just shift it for one year back which is why we use minus one in the formula. That's it. This formula takes my revenue and just shifts it for one year back. Close it and we have the revenue from previous year. Now you have the the basic conditions, the minimum model in Power BI to start building your comparisons. Now I add this revenue from previous year into my visual, and now the magic starts to happen. Only now can we start building meaningful reports in Power BI.

Looking at variances

As you may have noticed, the visual has changed completely because now our visual understands that you are working with two values. You have actual values and the previous year. Our visual calculates the sum of the previous year for the whole year 2017. It is 6.5 million. On the other side, we have the actuals at 8.1 million. The visual already calculated the growth and now everything else happens automatically. You have the growth from the previous year expressed in percentages and if you click it, you will have it in absolute values, or you can click again to display both. You now have year over year values for each variance for each month.

You can just simply switch between different types of charts and just do a simple line comparison. Or you can use area charts and column charts with different types of layouts, here. So, if you have very limited space, you can just use a column chart with the variances. This is now the growth for each month, here, and if you enlarge it, the visual will calculate the relative growth for each year. You can see the relative variances here.

If it's even larger then, you can display it in a much, much better way. This is really nice, because you can have a very small chart here and other things around it, but the the user can then simply open a chart in the Focus mode where everything is big and the relative variances are calculated.

My personal favorite here is a waterfall chart. It's good, because it just focuses on the most important things. If you want, you can break the axis just to temporarily see the variances better. Otherwise, just don't do that.

Combining multiple visuals

Now we can start building dashboards by combining multiple visuals. I enabled the previous year comparison and I can already create a dashboard page. For example, I'll take another visual and I'll just do a comparison to previous year by another dimension. We'll use the group of business units and do the comparison of actual versus previous year. You can again see that our visual is fully responsive. In a single chart you have the actual, the previous year and the previous year in percent. You can do very interesting analyses already with the two visuals and previous year comparison. For example, you can sort and see the worst performing business units and then click on it to see the details in the monthly details.

As you may have seen, we have very different business units here: baby care, wearables, audio hardware and so on. It's kind of a mixture, because this is a company that deals with different types of businesses, and that's why it would help to add another hierarchy level here. I'll just add the Division field into category just to understand this business better. As you see, we have electronics and then some personal care. We see that this is a completely different business here.

You can now just click on the level to see the result for electronics, the wearables and mobile products and so on. This is already starting to make a little bit of sense. An even better decision is not just to have one chart on the right but to present all the business unit groups here. You can do this by just adding the group to the chart. Let's make the left one a little bit smaller, and the right one a little bit bigger. This is just another way to build a dashboard page.

Now you see the trends for each one. The right one is the small multiple so you see the trends for everything. I'll switch back to my favorite, the waterfall chart, which will look much, much better if you clean it up with proper labels. You need to figure out your units. I'll just report in thousands. It's much better to just use the K, M, and B suffixes, which Power BI always puts in as default. Simply put them in the title. Now we have revenue and revenue versus previous year comparison shown in K.

I'll also reduce the number of decimal places. In some cases, you can also reduce the label density to make things cleaner. Some charts need a little bit more space, but you can actually control this by setting the category width. You can control the category width by setting the fixed category width. This allows you to say the maximum width of your columns is 22 and make all the columns exactly 22 pixels wide. Now this chart makes sense. Now the user can analyze data to just see all the business units or just look closely at what's going on within electronics.

I still prefer the waterfall chart. You can also crop the axis or just click on the personal care. You see these four product categories and it's obvious that you have one big business unit here in your personal care division. If you have data visualization challenges like this - with big and small charts - you can use the advanced layout in small multiples. The automatic layout will try to see if there are some large values and will try to arrange other charts around it for a better placement. You can also play around with the so-called largest-first algorithm, which will just take care of really big charts. This usually provides better results if you have large differences between data categories.

Creating the YTD switch

This chart is already starting making sense but we need to move forward. The next thing would be to create the year-to-date switch. Year-to-date switch will use the month and I'll take the month and use the month as a filter on the page by using the Slicer visual. The user can simply select their month, like August. Of course, the slicer filters all the visuals on the page, so when you switch to September, the table part of the dashboard is okay but the trends are broken. Fix this by clicking on the slicer, selecting Format settings and selecting the Edit Interactions button.

You want this slicer to control everything but the time series. When a month is selected in the Month slicer, it's not affecting the time series. This is just the simplest way to control which visuals are affected by a slicer.

Now we need the switch. The switch is used so you can look at data for August or August year-to-date. How to do this in Power BI? Let's switch to the data view where we will add another table.

Or we can do it in the Relationship view. We'll add another table for the monthly versus year-to-date switch on the dashboard. This is called a disconnected table, because it's just going to be a switch with two values, and we'll do it by clicking Enter data and we'll just add another table which I we can call Period calculation.

Let's add the first column. This will be my period calculation. The next one will be a number and we'll just call it calculation ID. We'll put "monthly" in the first column. This is the first option, option number one. Option number two is year-to-date. Number two. We created this little table here and it should be placed somewhere near your calendar.

When you go back to your model you have the period calculation column in the Fields list that you can just move to the dashboard. You now have have monthly and year to date options. Change this visual to a slicer again, but make it horizontal this time.

Now you have a nice little button here where you can simply click on monthly or year-to-date values. Of course, nothing is happening yet, because it's not related to other visuals, so you need another Power BI DAX trick here. Create a new measure and call it Selected calculation which will simply return the selected calculation. I'll do this by using the MIN DAX function to return the minimum calculation ID. Here it is. Alright, and that's it. It's really simple.

SelectedCalc = MIN('Period Calculation'[CalcID])

I'll just drop this into my report and display it as a Card visual. It's working. How will I link this to my model? I will go back to my Revenue AC measure, which at the moment simply returns the filtered column from my sales fact. I'll do this in two steps. I will just rename this measure. I'll call it filtered - Revenue AC filtered. Now, I'll add a new one that will take into account the selected calculation and if the value is 2, calculate the year to date value.

This will be my true Revenue AC measure. How do I do the switch? What's the function name for that in DAX? Switch.

Revenue AC = SWITCH ([SelectedCalc],
    1, [Revenue AC filtered],
    2, CALCULATE([Revenue AC filtered], DATESYTD('Calendar'[Date])))

You evaluate the expression. In my case, I am going to evaluate the selected calculation. Let's go into new line by pressing SHIFT + ENTER. And now, if the value is one, the formula simply returns my previous measure, which is now called Revenue AC Filtered.

In the second case, when I need the year-to-date value, I will calculate my Revenue AC Filtered. I need the year to date value and there's a function for this that's called DATESYTD. That's year to date, and you just need to add the date column from the calendar table and we close it off. Now my Revenue AC measure just evaluates the selected calculation from my switch, and does the switch, and calculates year-to-date if necessary.

This measure is prepared. So, let's click on the visual, I'll just throw out the old one, and throw in the new one, and hope it works. It's very green because now I'm comparing my actuals. This is now year-to-date, and comparing to my previous year. Previous year still refers to my old measure, so I'll just correct this. Previous year will be calculated from the new one, so it'll work for monthly and for year-to-date. You see, all these different options. How to display the year to date.

Let's fix the design by removing shading by setting opacity to 0%. We need to fix the table by using Revenue AC measure, which takes into account the monthly, the year to date, and will work across the whole dashboard. All of this is happening in the same visual. There's just one visual and everything else is done with switches and dropdown menus.

Creating a KPI switch

That's basically all of the Power BI DAX that I wanted to show today, but I do have one more trick. Trick number five, where we'll use the same idea with the switch, but this time I will use it to select the KPI.

We want to change the charts from revenue to gross profit. Right now, we have done this for revenue and we have revenue, actuals and previous year. I could also do this for the plan. Let's do the plan quickly. I will copy the existing formula for revenue and simply filter by the plan. This'll be my revenue for the plan. Click New measure and use the below formula.

Revenue PL filtered = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Scenario]="PL"))

So, this is my plan filtered, and now I need the plan, the measure for the plan, that it'll also take the account the same switch. Create a new measure and paste in the below formula:

Revenue PL = SWITCH([SelectedCalc];
    1; [Revenue PL filtered];
    2; CALCULATE([Revenue PL filtered]; DATESYTD('Calendar'[Date])))

This is the switch. This will return the normal plan and in the second option it'll return the year-to-date values of my plan. Now I've got the actual, the plan and the year-to-date. However, all of this is just for the revenue. Imagine if you wanted to do this again for the gross profit, one option is to do all of that again for the gross profit. Same thing just for different KPI.

You could just copy and paste the same DAX for the Power BI measures. But if you have five, three, five or ten measures, that's a lot of measures. And another thing is when you want to use those measures on your report, you need to create another visual, so you need one visual for the revenue, the second one for profit and so on. But I'll do something else. I will use a switch and I'll just reuse the same measure.

The idea behind it is to add another disconnected table. Let's do the same here. Click Enter data in the data section and repeat the whole procedure from before. This time I will call this my KPIs table. My first column is the KPI, and the second one will be a number: one, two, three. I have three KPIs, so I'll just name this KPIID. My first one is the revenue, and this is KPI number one. Then I have my cost. That's number two, and the third one is the gross profit. This is now number three.

I load this into my model and I've got another disconnected table. I can use this KPI now as another switch.

I will change it to a slicer visual. I want this to just reflect in my dashboard. Make this into a dropdown because it's nicer and it'll fit the top navigation bar. I'll create a measure that just returns the selection, and I'll call it SelectedKPI. Again, I will use the MIN function , which will return the minimum KPI ID, like that.

SelectedKPI = MIN(KPIs[KPIID])

This is now my selected KPI. We can switch between revenue, gross profit and costs. The order here is not really what I wanted. Everything is sorted alphabetically in Power BI by default so I just need to set this in your Data view. Select the KPI table and set that KPI needs to be sorted by column KPIID, which will now get the measures in the right order.

Revenue is number one, cost number two, gross profit number three. That's the custom sort, exactly how I want it. Now, I will just use another SWITCH formula to return the measure that I want.

I will create another measure. I will call it Selected value. The below formula will calculate the sum of my revenue as the first option. The second option will return cost. Number three is the last one and will return gross profit. This switch simply returns a different column based on the selection in the slicer.

Selected value = SWITCH([SelectedKPI];
    1; SUM(Sales[Revenue]);
    2; SUM(Sales[Cost]);
    3; SUM(Sales[Gross Profit]))

This is now my Selected value measure and we just need to correct my measure from a revenue actual to a generic actual value which will return not the revenue actual but the ... Actually, I have a better idea. I can do it here. Instead of returning actual for the revenue, I will return the actual for my new measure Selected value. And that's it. This is now not revenue anymore, it's actual data filtered and it uses the selected column.

This is AC filtered, and now I go to my revenue AC, and this is already working, I will just rename it. This is now my generic actual measure that takes into account the year-to-date and the selection of the KPI, the selection of the column. Let's see if this works: Gross profit revenue. It's working.

Putting it all together

And now this is a completely generic dashboard. It works for everything, and you can simply use one visual and everything will work. Now you can create pages in minutes. Let's just put those slicers in order. In a dashboard, I would first put the revenue, then I would put the month, and then the year, and then my period calculation. If you want to save your dashboard real estate. You can remove some of the headers for month, year, and so on. You can simply put the slicer header off and you could just keep it completely minimal and use the space for something better, which is the visualization tables and everything else.

You've got your page and you can simply just copy the page and use the same thing for something, you know, for different types of things. For example, let's make this a big one. You can add the plan and now you can build tables and compare previous years and actual plans by including variances that you can just reshuffle around. You can do this on this nice little page with only one visual.

You can add details like business unit and add more levels. You can create a hierarchy, like a multidimensional, multi-level hierarchy. You can even do crazy things like removing the slicer. What happens if you remove the slicer? Everything still works. That's the magic of the MIN function, because even if the slicer is not there, the minimum is still evaluated, and it will return the option under one.

I just wanted to show you something else. Instead of showing the business units under Group I will use my KPIs. I'll put my KPIs into the Group data field and now I've got small multiples of the measures. This is something that I think is completely impossible to do in just vanilla Power BI, because now I have my revenue and I have my gross profit, basically, scaled, in a small multiple. This helps you understand what is the relation of gross profit to revenue? I can also filter this by revenue and gross profit for comparison. You can also do two-dimensional multiples, multi-dimensional multiples.

To do this I'll just group by another field here, which creates the two-dimensional small multiples. I've got revenue and gross profit for all my business units, or divisions, and so on. YXou can switch from monthly to year to date. You can compare the revenue and gross profit and everything else. It's very easy to build dashboard pages and you can just create 10 dashboard pages in like an hour or something.

Conclusion

These are my top five Power BI DAX tricks for today. That was it. I hope you enjoyed it. I will share all materials with you in the recording. Download Zebra BI visuals. This is the download link, so there's a free trial available. If you want to try this on your own, please just go to this website. If you don't have them, download them, and you'll get the free trial so you can play around. We've got the resources on our web page. We've got a help section with a lot of PBIX files, so Power BI files that you can simply download from the previous webinars and income statements and other stuff. It's quite interesting.

And we've got the webinar recordings on this link, so make sure you check the webinar recording. There's 19 hours of the webinars, of the recording material, already, on this link. So all our previous webinars, from Power BI, Excel and reporting. And if you need help, just shoot us an email at [email protected]. Somebody will help you, and follow us. If you like this, if you like the ideas, please follow us. You know, mention us, please. Just help us spread the word about this, and make sure you're subscribed to our channels and so on so you'll get notified about the next webinars, and videos, and other stuff that we are doing.

Hope you've enjoyed this one. Thank you very much, see you at our next webinar. Bye.

Watch recording and get PBIX examples

Enter your data below to get immediate access to the full 1-hour video recording of the webinar and all PBIX examples used in the webinar.