Sum all Related Records and Multiply by a Column in a Related Table

[drgrundy]

1) I have a table of billing transactions which I need to sum the "Units" by "Rate Code"  Resulting in Total Units per Rate Code.

Billing Transactions

2) Each "Rate Code" from the the billing transactions table is then related to a "Product Code" in the product catalog table.

3) A table of Taxes (Prod_Lvl_Exp_Rate) applies an amount of tax to each "Product Code" which is then related to the product catalog table.

Taxes Table

I need to come up with a way to multiply the tax, (Prod_Lvl_Exp_Rate), from the taxes table * the SUM of the "Units" of all related "Rate Codes" in the billing transactions table.

[v-yuta-msft]

@drgrundy ,

The expression is dependent on the relationship between the three tables, could you please show the relationship like "one-one", "one-many" between these tables?

Regards,

Jimmy Tao

[drgrundy]

Billing Transaction Table relationship to Rate Code Table is a Many : One relationship

Taxes Table to Rate Code Table is a One : One relationship

[v-yuta-msft · Accepted solution]

@drgrundy ,

Create a calculate column using dax as below:

Result =
VAR Current_Rate_Code = 'Billing Data'[Rate Code]
VAR Total_Units_per_Rate_Code =
    CALCULATE (
        SUM ( 'Billing Data'[Units] ),
        FILTER ( 'Billing Data', 'Billing Data'[Rate Code] = Current_Rate_Code )
    )
RETURN
    Total_Units_per_Rate_Code
        * CALCULATE (
            SUM ( 'Product Level Allocation Exp'[Prod_Lvl_Exp_Rate] ),
            FILTER (
                'Mod Expanded Rate Card',
                'Mod Expanded Rate Card'[Rate Code] = Current_Rate_Code
            )
        )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

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

Maybe RELATED or RELATEDTABLE?