Forum Discussion

etane's avatar
etane
Helper V
1 year ago
Solved

Power Query (Dataflow) Sum a Column from a Different Table

Hello.   Is there an easy way to sum a field that sits in a different table using Power Query (Dataflow)?  Below is an example.  I have a Sales Table that tracks transactions by customer.  And, I h...
Dataflow
Tips and Tricks
  • etane's avatar
    etane
    1 year ago

    Thank you for this.  However, I need to build the data in Dataflow because I have to append this table with another table for a combined sales history table

     

    At the moment, I am working with someone in our IT department to do the table building via SQL ands storing it in a local server for me to pull the data.

v-veshwara-msft's avatar
v-veshwara-msft
Community Support
1 year ago

Hi etane ,
Thanks for posting in Microsoft Fabric Community,
Since you prefer to avoid M code, you can achieve this using a calculated column using DAX instead of Power Query.

To calculate the total number of engines per transaction, follow these steps:

  1. Confirm that there is a relationship between the Sales and Product Sales tables on the Transaction # field. [Product Sales----->Sales (Many to One)].

  2. In the Sales Table, create a calculated column using the following DAX formula:

    EngineQty =
    VAR TransactionID = Sales[Transaction #]
    RETURN
    CALCULATE(
        SUM('Product Sales'[QTY]),
        'Product Sales'[Transaction #] = TransactionID,
        'Product Sales'[Part Type] = "Engine"
    )

     

  3. This formula filters the Product Sales Table for rows matching the transaction number and where the Part Type is "Engine", then sums the QTY values.

     

  4. After applying this calculated column, Sales Table will now display the total quantity of engines per transaction like below.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards,
Vinay.

 

etane's avatar
etane
Helper V
1 year ago

Thank you for this.  However, I need to build the data in Dataflow because I have to append this table with another table for a combined sales history table

 

At the moment, I am working with someone in our IT department to do the table building via SQL ands storing it in a local server for me to pull the data.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi etane ,

    Thanks for getting back.

    You can achieve this in Dataflow using a custom column. 

    Since you mentioned working with your IT team to build the table using SQL and store it in a local server, this could be another viable approach.

    Hope this helps you achieve your requirement! If you have any doubts or need further assistance, please feel free to reach out.

     

    Please consider marking the helpful reply as Accepted solution to help others find it quickly.
    Best Regards,
    Vinay.