DAX Measure to Divide row value by filtered row value from same table

[Logesshwar]

I have 2 Tables 

Problem:
I need to calculate Fair Share Index (share of product in a region/ share of its cat in the same region), which is essentially division of ratios. The first part will have the Product Sales(Dollar Sales) in a Geography for a Time Period divided by Product Sales at National level from Table 1. The second part will have the Category Sales (Cat Dollar Sales) in the same Geography for same Time Period divided by Category Sales at the National Level from Table 2

I need a new Measure in Table 1 with this formula:
For each row: Index = [ (Table 1 row[Dollar Sales] / Table 1 [Dollar Sales] where [Geography] == "National" & [Time] == row[Time] & [Prod] = row[Prod]) / (Table 2 row [Cat Dollar Sales] / Table 2 [Cat Dollar Sales] where Geography == "National") ] * 100

Example:
Consider the Bold and Italicized Row in above Table 1. The calculation will be as follows,

Fair Share Index = [ (100/500) / (1000/10000)) ] * 100
= [ (0.2) / (0.1) ] * 100
= 200%

What I have Tried:

I have joined the Tables in the Data Model on Product Column with Many to Many cardinality and Table 1 filters Table 2.

I have tried implementing several type of DAX queries but everything throws an error. I'm new to PowerBI especially DAX. I have done the same in Python with no issues. So any pointers on how I can achieve this would be greatly appreciated. Thanks in advance

[v-jingzhang · Accepted solution]

Hi  @Logesshwar 

You can create a calculated column in table 1 with below DAX:

Column =
VAR a =
    DIVIDE (
        CALCULATE ( SUM ( 'Table 1'[Dollar Sales] ) ),
        SUMX (
            FILTER (
                ALL ( 'Table 1' ),
                'Table 1'[Product] = EARLIER ( 'Table 1'[Product] )
                    && 'Table 1'[Time] = EARLIER ( 'Table 1'[Time] )
                    && 'Table 1'[Geography] = "National"
            ),
            'Table 1'[Dollar Sales]
        )
    )
VAR b =
    DIVIDE (
        CALCULATE (
            SUM ( 'Table 2'[Cat Dollor Sales] ),
            'Table 2'[Product] = EARLIER ( 'Table 1'[Product] )
                && 'Table 2'[Geography] = EARLIER ( 'Table 1'[Geography] )
                && 'Table 2'[Time] = EARLIER ( 'Table 1'[Time] )
        ),
        CALCULATE (
            SUM ( 'Table 2'[Cat Dollor Sales] ),
            'Table 2'[Product] = EARLIER ( 'Table 1'[Product] )
                && 'Table 2'[Geography] = "National"
                && 'Table 2'[Time] = EARLIER ( 'Table 1'[Time] )
        )
    )
RETURN
    DIVIDE ( a, b )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.