Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
akhawar
Regular Visitor

Calculate Measure across multiple FactTables in a Data model

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. 

 

Store_Profile and OutletData.jpg

 

 

Greatly appreciate any help, hints and suggestions!

2 REPLIES 2
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.