Forum Discussion

sree2604's avatar
sree2604
Regular Visitor
1 year ago
Solved

Need Help with Data Model / DAX

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 t...
  • sanalytics's avatar
    sanalytics
    1 year ago

    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

     

     

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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)