Forum Discussion

CA8172's avatar
CA8172
Helper I
7 years ago
Solved

Calculating multiple columns from two different tables

I have two tables (see below), the first table has "Items" Purchased by "Store", the second table has "Items" Sold by "Store".             I would like to calculate in a new table (see b...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi CA8172 

    Is this problem sloved? 

    If it is sloved, could you kindly accept it as a solution to close this case?

    If not, please let me know.

     

    I have a solution as below:

    Assume your data is like

    In Edit queries,

    1. unpivot other columns for "purchased" column, the same for "sold"

     

    2. Rename columns, then groupby columns

    The same for " Sold" table(Table 2)

     

    3. Then merge two tables, expand value

     

    Finally, close&&apply, create a measure

    Inventory = SUM('Table 1'[purchased value new])-SUM('Table 1'[Table 2.sold Value])

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi CA8172 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

 

I have a solution as below:

Assume your data is like

In Edit queries,

1. unpivot other columns for "purchased" column, the same for "sold"

 

2. Rename columns, then groupby columns

The same for " Sold" table(Table 2)

 

3. Then merge two tables, expand value

 

Finally, close&&apply, create a measure

Inventory = SUM('Table 1'[purchased value new])-SUM('Table 1'[Table 2.sold Value])

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PANDAmonium's avatar
PANDAmonium
Resolver IV
6 years ago

Hi,

 

Thanks for helping explain that in screenshots. Though they still have to fix the issue with duplicate products. Your/their duplicates records at the same store are doubling purchased when expanded. See product Z at store 1 for reference. So without fixing the duplicates, it's doing: (purchased * 2) - sales = inventory which isn't accurate.

 

So they still have to either sum, remove, or rename those duplicate products depending on what accuratly reflects the data. Hopefully you can help them through this last step as well.

 

Thanks!