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.
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.
Using ‘ISINSCOPE’ is a better solution to this type of problem. MS released this function in November 2018. Refer to the following post by Kasper:
2 thoughts on “Non-additive measures with different granularity in Power BI”
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.
Sure, let me know details. I will try..