Calculate Measure across multiple FactTables in a Data model

[akhawar]

Hi,

New to PBI and really enjoying it. Am running into an issue that I'm sure should be do-able with DAX measures. I am looking to calculate number of stores that have purchased a given item. See attached picture of my data model. My PBIX file can be downloaded from here: https://drive.google.com/file/d/1OS02Wb-M8OGlhTTNHwEqUPKfTY82Wl5i/view?usp=sharing

Here's a brief description:

ProdGrp: is grouping of items and categories. It's linked through item to:

SalesData: that shows what item was shipped/sold to what store and associated Revenue. 

Store_Profiles: is linked to SalesData and connects store attributes given in

StoreFronts: This has the total count of store outlets per year. This has to be averaged for a given store over a period. For example if both years 2017 and 2018 are selected the outlet sum for Store A should be the average of the sum of outlets in 2017 and sum of outlets in 2018 (this measure is working for me I believe)

My goal is:

1. a measure that looks at item or category of Product and shows the sum of outlets for the store that purchased that item (or category). For exampe, in 2017, TV was only sold to store A. And Store A has total of 39 outlets. So, for 2017 the TV item should show the store outlets count should be 39. In 2018 only Store B and Store C purchased TV (total quantity 148). As their outlet sum is 86, that's the value i'd like to show. This measure has to work on the ProdGrp table as I need to show the same for Categories (Electronics, Appliances etc.)

2. Would be great if the above measure also allows to have a cutoff to ignore sales quantities below a given % (say 10%). So, for 2018 we see that Store C only purchased 3 TVs. Since it's below 10%, we ignore the store outlet count for C and only now show 10 store counts (instead of 21). So, the store names need to be ranked and only top 90-95% of the names need to be passed on to the measure for adding the outlets.

3. A second measure that shows the top n stores that purchased the item/category. Here I have only 3 stores but in my actual data i have hundreds of consuming categories so would like to show top 3 (or n)

My PIBIX file can be downloaded from here, that shows a few of my existing measures

https://drive.google.com/file/d/1OS02Wb-M8OGlhTTNHwEqUPKfTY82Wl5i/view?usp=sharing

The challenge (as I read) is that StoreFronts and SalesData are two FactTables. ProdGrp is a dimension table that is not linked to StoreFronts table directly. However, I am hoping Dax should be able to pass on the Store IDs from SalesData table to StoreFronts table and use those IDs to calculate the sum of outlets. 

Greatly appreciate any help, hints and suggestions!

[Greg_Deckler]

Would it be possible for you to post that date as text? That is a lot of data to type in by hand. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[akhawar]

Thanks for the advice. I have posted a link to my pbix file in the original message. No more data entry needed 😉