Filtered Data vs. Unfiltered Data In A Matrix In Power BI

  • Post category:General
  • Post author:

Hey there!
Today I am gonna discuss about creating the custom groups using calculation items.

Imagine yourself in a situation where you as a product analyst is planning to choose the color variants for a new product that your company is designing. You must be looking for some data that could display you the sales performances of the similar products that are in the market based on the colors.
I also agree, this will surely be giving you a deeper insight about the customer preferences…

What we will generally think of is a matrix that will display the Sales Amount of all the colors, and we will just simply apply a Color filter to them.
But…
What will you do when you wish to see the sales of the filtered colors vs. sales value of remaining colors in another column next to it!
It must be sounding weird!!!

Let’s go with an example…

You have filtered the matrix based on the Black and Blue color. You have filtered the colors using slicer. But you need another column that will show the sales values of all the remaining colors- Red, Silver, White, and Yellow.
Here is the proposed view-

That seems interesting, right?

Let’s create this kind of matrix using simple DAX…

Before writing the DAX, I prefer to talk about my data. I am using AdventureWorks data, that is a sample data provided by Microsoft.

These following are the tables that I am using-

I will be leveraging Sales and Product table out of these tables.

Steps-

  1. Now, I will create a measure that will simply take Sales Amount from sales table-

This will give me the Sales Amount for the selected values in the Color slicer.

2. Now, I will create another column that will be able to calculate the Sales Amount for all the unfiltered colors i.e., for the values that are not selected in the slicer.

3. Before plotting the two new measures, we will create a separate table of distinct color values.

4. Now, I will plot these two measures in a Matrix visual along with the newly created distinct column of Colors

That’s it. We are done.

Further if you want to have the sales value for all the columns ignoring the filters, you can create another measure-

This will facilitate you with a comparative matrix for the Filtered Data vs. Unfiltered Data vs. All Data-

I hope you must have found this blog useful.

Thank You for reading.

Addend Analytics is a Microsoft Power BI Gold Partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com or Contact us

Rajeshwari Sharma 
Data Analyst 
Addend Analytics