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 ho...
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.

v-xiaosun-msft's avatar
v-xiaosun-msft
Icon for Community Support rankCommunity 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!