Forum Discussion

ppc_raltandi's avatar
ppc_raltandi
Frequent Visitor
4 years ago
Solved

SUMX from two tables

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?

Need Help
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    4 years ago

    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.

4 Replies

  • ReneMoawad's avatar
    ReneMoawad
    Resolver III
    4 years ago

    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])
        )
    )



  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    4 years ago

    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's avatar
      ppc_raltandi
      Frequent Visitor
      4 years ago

      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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

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