Need Help with Data Model / DAX

[sree2604]

I am working on making a dataset more user friendly / light in size. Data Model along with DAX look very promising and would greatly appreciate any help to achieve the below.  

Problem:  I need to calculate Sales per million lives for each product by using the below 2 tables.  I am looking to have one measure for the power pivot which can calculate Sales per million lives at any area hierarcy level (Country / State / District level), problem being few areas which have lives may not have any sales.  So for the Sales per million lives calculation at country level .e.g Country B need to have total lives at 50m including last 2 rows in table 2 ($3000 divided by 50).  This measure needs to aggregate lives for the area selected at any level.  Can we use DAX formulas to create such measure ?  
I could acheive this witha a single measure in power query by adding empty sales rows to make the lives whole for each product but this is essentially doubling the table 1 size for my dataset (shown in the last table below), hoping DAX could achieve this without this workaround
 - Table 1 has Sales by Product details.  This is my primary table which has many other fields & Products but not showing here for simplicity

Sales Rep	District	State	Country	Product	Sales
AAA	YYY	State A	Country A	Prod A	$1000
BBB	ZZZ	State A	Country A	Prod A	$1000
CCC	XXX	State B	Country B	Prod A	$1000
CCC	WWW	State B	Country B	Prod A	$1000
CCC	VVV	State B	Country B	Prod A	$1000

 - Table 2 has Lives at Country / State / District / Rep level.  the last 2 rows are combinations which do not have any sales but those lives need to be included in Sales per million lives calculation for those respective state / country 

Sales Rep	District	State	Country	Lives (in M)
AAA	YYY	State A	Country A	10
BBB	ZZZ	State A	Country A	10
CCC	XXX	State B	Country B	10
CCC	WWW	State B	Country B	10
CCC	VVV	State B	Country B	10
DDD	UUU	State B	Country B	10
AAA	PPP	State A	Country B	10

Current workaround in Power Query: to calculate Sales per Million lives I am currently using power query to add empty sales rows to make lives whole for each product and then created a calculated field in pivot to calculate Sales per million lives.  The single calculated feild does Sales per Million lives calc at any area level 

Sales Rep	District	State	Country	Product	Sales	Lives (in M)
AAA	YYY	State A	Country A	Prod A	$1,000	10
BBB	ZZZ	State A	Country A	Prod A	$1,000	10
CCC	XXX	State B	Country B	Prod A	$1,000	10
CCC	WWW	State B	Country B	Prod A	$1,000	10
CCC	VVV	State B	Country B	Prod A	$1,000	10
		State B	Country B	Prod A		10
			Country B	Prod A		10

[v-menakakota]

Hi @sree2604 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Thank you @sanalytics  for providing possible solution,that mentioned  separating the data model into two fact tables with shared dimension tables really make a difference. Since the Sales and Lives data are at different grains, trying to force them into one table (like I initially did in Power Query) was increasing the model size unnecessarily.

After restructuring the model (with a proper star schema), calculate Sales per  Million Lives using clean DAX without needing to duplicate rows.

Total Sales := SUM(FactSales[Sales])
Total Lives := SUM(FactLives[Lives (in M)])

Sales per Million Lives :=
DIVIDE([Total Sales], [Total Lives] * 1000000, 0)

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[sanalytics · Accepted solution]

@sree2604 
Please create proper data model rather than only create DAX.. beacuse DAX works on relationship.. Your two tables have different grain so you should have two fact table.. Below is the complete screenshot of your data model

You need to have a simple star schema

Complete pbix file is attached below.

Hope it helps

Regards

sanalytics

[sree2604]

worked like a magic!  thank you so much 🙂
appreciate you for sharing the file with the solution, mapping the tables back in the data model and clearly showing how to create the relationships to solve this.

[bhanu_gautam]

@sree2604 First, ensure that you have a relationship between your Sales table (Table 1) and Lives table (Table 2) based on the appropriate columns (e.g., Sales Rep, District, State, Country).

Create a measure to calculate the total lives for the selected area:

Total Lives :=
SUM('Table 2'[Lives (in M)])

Create a measure to calculate the total sales:

DAX
Total Lives :=
SUM('Table 2'[Lives (in M)])

Create a measure to calculate the total sales:

DAX
Total Sales :=
SUM('Table 1'[Sales])

Finally, create a measure to calculate Sales per Million Lives:

DAX
Sales per Million Lives :=
DIVIDE([Total Sales], [Total Lives] * 1000000, 0)