Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Summing Qty from two different data sources based on a second column in each data.

Hello everyone! 

 

I am trying to Sum Qty from two different data sources. The data sources both contain a column that has customer names. 

So i am trying to sum the Overall Qty per customer.

 

Here i have an example. I tried using the quick messure to add qty on qty but that only worked overall not for individual customers. 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to create a distinct dimension table and create relationships among them:

    DimensionTable = DISTINCT(SELECTCOLUMNS('Test-1',"CustomerID",'Test-1'[CustomerID]))

    Then create a measure to sum all Qty from two tables:

    Measure = SUM('Test-1'[Qty])+SUM('Test-2'[Qty])

    Choose [CustomerID] from DimensionTable and this measure as a table visual, it shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Create a common dimension and join both tables with that.

    You can create like

    customer = distinct(union(all(table1[customer ]),all(table2[customer ])))

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try to create a distinct dimension table and create relationships among them:

    DimensionTable = DISTINCT(SELECTCOLUMNS('Test-1',"CustomerID",'Test-1'[CustomerID]))

    Then create a measure to sum all Qty from two tables:

    Measure = SUM('Test-1'[Qty])+SUM('Test-2'[Qty])

    Choose [CustomerID] from DimensionTable and this measure as a table visual, it shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi