The above picture shows a simple design of the former table as you can see, we have header data at the top of the table name Age Group. However, to better analyze data and add more calculations and charts and, in general, get more insight from data, we need to change the matrix data in Excel to a tabular format in Power BI.įor example, below table, we have data about the Deaths from Cancer of Lip, oral cavity, and pharynx by age, sex, and year.Ī simple look at the data can be seen below. Please don’t forget to subscribe to the Enterprise DNA TV channel because we have a huge amount of content coming out all the time from our experts.There is a common way of seeing data in Excel as a Matrix format many data entries look like a matrix in many contexts, from accounting to health data. I hope you’ve learned a lot from this simple technique. Noticeably, it’s easy to calculate a ranking when fields from different dimension tables are brought together in a tabular form in Power BI.Īlso, there are several rules where ranking is tied so if you want to learn how to break ties in Power BI, you can check out RANKX In Power BI | Developing Custom Tiebreakers tutorial as well. The ranking is a great way to see the best or worst-performing variables in a given data set. Temporal Scale Using Calculated Columns In Power BIĬALCULATE Function – How It Can Affect Your Calculations In Power BI Conclusion So there, the ranking at a total level is ignored as well.Ĭalculate Percentage In Power BI In Hierarchical Form

This will ignore the results at a total level. We also want to exclude the ranking at a total level by typing in IF(ISINSCOPE( Customers ). We can now see that it’s calculating the Total Sales only where we have sales figures and the rest of the combination of where we don’t have sales are ignored. To do that, let’s add IF (ISBLANK(), BLANK ( ). To rectify this kind of scenario, we need to insert and IF condition where we will calculate the ranking only where we have Total Sales else it should be blank. However, since we used the CROSSJOIN function, it creates a combination of all the rules between the customers and the products which resulted in evaluating the ranking against those rules where we don’t have the figures for the Total Sales. The next thing to do is call out our Total Sales measure and bring this ranking measure inside our table visual to analyze the results.Īs we can see, we are getting the correct results. Then, use RANKX and CROSSJOIN functions.īy using ALL function, we also need to reference our field names one by one to Customer Names, Product Names, and Product Index field. We can now create our measure to evaluate the ranking at a combined level of customers and products. In addition, since we know that the Product Name is being sorted by the product index field, we also need to include that field while evaluating the ranking.Īs we can observe, the two fields from uplifting tables are coming together inside one table visual hence we will implement the CROSSJOIN function inside our RANKX function while referencing the table names. One of the things we should do when we start writing the measure for ranking is to reference the field names along with the table names inside the RANKX function. We will analyze at a combined level which is our best-performing customers in combination with the product names based on Total Sales.

In this scenario, we created a table visual with the Customer Name field from the customer’s table and the Product Name field from the product’s table. Let’s jump onto the Power BI to kick it off. You can watch the full video of this tutorial at the bottom of this blog. Today, you will learn how to calculate the ranking in a tabular form when fields from different dimension tables are brought together inside the table visual.

Power bi matrix show in tabular form