Non-additive measures with different granularity in Power BI

I have found many articles on web regarding non-additive measures in power BI. But most of them are about inventory-type measures where normal aggregate functions are not suitable for use. However, in real life we often face situations where data providers deliver pre-calculated measures for different hierarchy levels. Numeric distribution, weighted distribution, market share etc. are some examples of such measures. And then we are left with no choice but to use fact tables with different granularity. It’s always a bit of challenge to show those measures in a single report in any BI tool.   

Look at following example. Here, we have a non-additive measure called Market Share and two fact tables with different granularities. One of them is at Region level and the other one at Country level.  

CountrySales Table

RegionSales Table

Let’s define a DAX measure called ‘MS’ in CountrySales table:

MS = if (selectedvalue(CountrySales[Country])=BLANK(), CALCULATE(SUM(RegionSales[Market Share]), filter(CountrySales,CountrySales[Region]=SELECTEDVALUE(RegionSales[Region]))), SUM(CountrySales[Market Share]))

Let’s look at the visuals now:

As you can see, Sales are getting summed up but MS (DAX based market share) are showing value directly from RegionSales table. I have used multi-select slicers here. Whenever all or multi-country is selected from Country slicer, a blank is returned and Region level MS (Market Share) is displayed. Otherwise Country level share is displayed.

In case you are wondering about relationship:

This solution can be improved a lot. I would be happy to hear from experts.

About Zahid

10+ years of experience in BI and DWH technologies. Fields of expertise are BI Reporting (MSBI, Microstrategy, Excel, Power BI) , ETL, Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in Power BI, DAX and tagged , , , , , , , . Bookmark the permalink.

2 Responses to Non-additive measures with different granularity in Power BI

  1. Narges says:

    Hello Zahid, Thank you for your post. Actually I have a special case for which I’m trying to find a solution, but up to now I haven’t found proper and convincing solution honestly. I’m almost new in Power BI and really need to resolve my issue. Can I please ask you? That would be helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.