Weighted and numeric distribution in Power BI Desktop
Learn to use the SUMMARIZE and CALCULATE functions to produce numeric and weighted distributions.

Posted by Sam Lowrie on 18 May 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Weighted and numeric distribution in Power BI Desktop

For my (fictitious!) sales company, I would like to calculate the number of stores each product has sold in, then work out the percentage that product makes up of all the sales in those stores.

Make a mammal database model

The world of cuddly toys is pretty cut throat....

First up is to work out the number of stores my products each sold in. To do this create a table visual, then count the CentreID field in the tblCentre table and count distinct the CentreID field in the tblPurchase table.

Measures calculate count relationships

Interestingly this results in two different results. The tblCentre table's Centreid field is returning a count of ALL the centres. rather than the count of stores that have sold something.

 

This is caused by the direction of filtering. We want to use the product table to filter the centre table but that would be going against the relationship flow:

Power BI Relationships

The products table and centre tables can filter the purchase table. They can't currently filter each other.

Double click on the relationship you want to change. For this example I want the Products table to filter the Centre table so I double click the Purchase/Centre relationship.

Cross filtering power bi desktop

This will open the relationship window. Change the Cross filter direction to Both to allow the Purchase table to filter the Centre table.

 

Now you should have the same product results for both columns of your table. Note the totals are different due to some stores selling no products:

Measure relationships cross filtering

We will need this relationship for later when calculating the weighted distribution.

Next I want a quantity sold total for each product that will not be affected by any external filters. To do this I will need a measure like this:

Measures calculate filters

CALCULATE replaces filters applied to the expression with ones you specify.      ALLEXCEPT stops the product filter applied on the row from being removed.

So now I have a quantity for each product that can be filtered using slicers or filters, and another which stays the same.

Filtering Calculate

Bob has sold 272 units of which 8 were in a Factory Outlet store.

Now we can calculate the Numeric Distribution by dividing the number of stores the product sold in by the total number of stores:

Measures calculation percentages

Divide the number of stores the product is sold in (filtered by the row) by the total number of stores. All replaces the row filter and returns all the CentreIDs.

We can now see the numeric distribution of each product (how many stores they are in/how many they could be in) : 

Stores sold in vs total stores

Bob is sold in 93 stores out of a possible 386 making up a measly 24% coverage!

Now to work out the total units sold for all products in the stores each product is sold in. For example: Bob has sold 272 units in 93 stores; how many units total were sold in those stores?

Measures All Calculate

This looks the way to go - sum the quantity sold for all the products.

Sadly this doesn't work and instead returns a total for all the products for all the stores:

Dax total all products calculate fail

The same has happened as we saw previously - Power BI has replaced the row filters.

We need to retain the filter that has been applied to the CentreTable by each product.  To do this we can use the SUMMARIZE function to create a table.

Summarize

SUMMARIZE needs a table and at least one existing column, then a name for any new columns you want to create by grouping a measure to every row of the first column.

SUMMARIZE will have created a new table with a column showing the total products sold for each shop. This in itself is no use as it would return the same values every time:

Table

Since there are no filters on the Centre table at this point the results column is just the units sold in each store.

Instead we need to create this table (virtually) for each product, then sum that column, giiving us all the units sold in stores that each product is sold in:

Summarize

We can pass the table into SUMX and choose the column we created [Total sales] to return the total sales in each store.

Add that to your table visual and it should looking something like this:

Measures Weighted distribution

We can now work out how Bob did compared to the rests of the products in his stores.

The last things to do is fairly straightforward: divide the the individual product's sales by the total sales in all their stores:

Finally!

You could retype the entire SUMX(SUMMARIZE) calculation again, but it's much easier to refer to the measure we just made.

There you have it folks - the weighted and numeric distributions for the Make-a-Mammal company! 

You can see what the resulting Numeric and Weighted Distribution Report looks like by following the aforementioned link.

This blog has 0 threads Add post