SUMX from two tables

[ppc_raltandi]

Hi all,

I'm breaking my head with trying to figure out a calculation in my Power BI workbook. When using SUMX function I don't get my desired result and wish to have someone shed some light on how to tackle my problem.

Below is an example of the relationship between my tables:

My desired Result is:

In order to get this, in Excel I can calculate it by getting the SUMPRODUCT of "Fraction" and sum of "Spend" for each person's Enrollment:

So for "Open" it's, (0.16 * 94) + (0.083 * 27) = $17.92  and for "Closed" it's (0.083 * 89) + (0.083 * 124) = $17.75

After that, I want to divide each result by the total "Fraction", so for "Open" it's 17.92 / 0.25 = $71.67 and for "Closed" it's 17.75 / .17 = $106.50

I'm having trouble getting this calculation to work in PowerBI, I think it's because it's duplicating the "Fraction" column, so if there are 4 fields in the "Subtype" table for one patient, it's counting the "Fraction" four times in the calculation, when I just want it to count it once. Is my model wrong?

[v-xiaosun-msft · Accepted solution]

Hi @ppc_raltandi ,

According to your description, here is my solution.

Firstly, make sure that the relationships between the four tables are available.

As @ReneMoawad  mentioned, create a table first.

NewTable =
CALCULATETABLE (
    SUMMARIZE (
        'Enrollment Type',
        'Enrollment Type'[Person ID],
        'Enrollment Type'[Type],
        "Fraction", CALCULATE ( SUM ( 'Enrollment Type'[Fraction] ) ),
        "Expenditure", CALCULATE ( SUM ( Expenditure[Spend] ) ),
        "Sum Product",
            CALCULATE ( SUM ( 'Enrollment Type'[Fraction] ) )
                * CALCULATE ( SUM ( Expenditure[Spend] ) )
    )
)

According to your description, I know that you want to calculate the division with the total “Fraction” and “Sum Product”. Therefore, we need to establish another measure.

Estimated =
DIVIDE ( SUM ( 'NewTable'[Sum Product] ), SUM ( 'NewTable'[Fraction] ) )

Final output:

Best Regards,
Community Support Team _ xiaosun

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

[ppc_raltandi]

You are a genius, sir! Thank you so much! I'm new to PowerBI and trying to get a hang of DAX still. In order for the Calculated Table to work with a filter I have I think I have to create a relationship to the calculated table, I got my expected result, thank you so much!

[ReneMoawad]

Hi,

Create a New Table with the below DAX 

NewTable =

CALCULATETABLE(

    SUMMARIZE(

        'Enrollment Type'

        , 'Enrollment Type'[PersonID]

        , 'Enrollment Type'[Type]

        , "Fraction", SUM('Enrollment Type'[Fraction])

        , "Spend", SUM(Expenditure[Spend])

        , "Sum Product", SUM('Enrollment Type'[Fraction]) * SUM(Expenditure[Spend])

    )

)

[Ashish_Mathur]

Hi,

Share the download link of your Excel workbook with your formulas intact for further clarity.