Create combined table from 2 tables from different tables and measures

[anastasiyaveko]

Hi all 🙂 

I'm stuck on a task that I haven't been able to complete for a long time. Please help :))

My goal is to understand how well or how poorly we cover customer needs, where we have missed opportunities.
And create table summary. 

Customer	Group	Product	Real	Potential	Year
Customer 1	Number of Sales	Bikes	171	500	2016
Customer 1	Number of Sales	Bikes	202	500	2016
Customer 1	Number of Sales	Accessories		100	2016
Customer 1	Number of Sales	Components		400	2016
Customer 1	Number of Products		1	3	2016
Customer 1	Number of Products		1	1	2017

I have the following data :

Calendar Table – A table containing date information.
Internet Sales – Number of orders for various products over the years.
Potential Sales – The potential number of orders that a customer could have in a year.

Potential Sales Table: 
CUSTOMER_NAME N_YEAR Date Potential Number of Orders Product
Customer 1 2016 01.01.2016 500 Bikes
Customer 1 2016 01.01.2016 400 Components
Customer 1 2016 01.01.2016 100 Accessories
Customer 1 2017 01.01.2017 200 Bikes
Customer 2 2020 01.01.2020 400 Bikes
Customer 2 2021 01.01.2021 500 Bikes
Customer 2 2022 01.01.2022 20 Bikes
Customer 2 2024 01.01.2016 500 Bikes


Internet Sales: 
CUSTOMER_NAME N_YEAR Date Number of Orders Product
Customer 1 2016 01.01.2016 1 Bikes
Customer 1 2016 01.01.2016 169 Bikes
Customer 1 2016 01.01.2016 1 Bikes
Customer 1 2017 01.01.2017 154 Bikes
Customer 1 2017 01.01.2017 1 Bikes
Customer 1 2017 01.01.2017 46 Bikes
Customer 1 2017 01.01.2017 1 Bikes
Customer 1 2018 01.01.2018 108 Bikes
Customer 1 2019 01.01.2019 281 Bikes
Customer 1 2019 01.01.2019 3 Bikes
Customer 1 2019 01.01.2019 44 Bikes
Customer 1 2019 01.01.2019 2 Components
Customer 1 2020 01.01.2020 95 Bikes
Customer 1 2020 01.01.2020 17 Bikes
Customer 1 2020 01.01.2020 4 Components
Customer 1 2020 01.01.2020 26 Bikes
Customer 1 2020 01.01.2020 222 Bikes
Customer 1 2020 01.01.2020 4 Bikes
Customer 1 2020 01.01.2020 64 Bikes
Customer 1 2020 01.01.2020 10 Components
Customer 1 2020 01.01.2020 1 Bikes
Customer 1 2021 01.01.2021 96 Bikes
Customer 1 2021 01.01.2021 677 Bikes
Customer 1 2021 01.01.2021 2 Bikes
Customer 1 2021 01.01.2021 258 Bikes
Customer 1 2021 01.01.2021 6 Bikes
Customer 1 2022 01.01.2022 1 Accessories
Customer 1 2022 01.01.2022 50 Bikes
Customer 1 2022 01.01.2022 1 Bikes
Customer 1 2022 01.01.2022 150 Bikes
Customer 1 2022 01.01.2022 20 Bikes
Customer 1 2022 01.01.2022 91 Bikes
Customer 1 2022 01.01.2022 3 Bikes
Customer 1 2023 01.01.2023 6 Bikes
Customer 1 2023 01.01.2023 104 Bikes
Customer 1 2023 01.01.2023 38 Components
Customer 1 2023 01.01.2023 12 Components
Customer 1 2024 01.01.2024 24 Bikes
Customer 1 2024 01.01.2024 80 Components
Customer 2 2020 43831 1 Bikes
Customer 2 2021 44197 38 Bikes
Customer 2 2022 44562 12 Bikes
Customer 2 2023 44927 94 Bikes
Customer 2 2024 45292 8 Bikes
Customer 3 2020 43831 8 Bikes
Customer 2 2019 43466 2 Components


CUSTOMER_NAME
Customer 1
Customer 2
Customer 3


I would like to create a table that combines values from two tables, including Measures. 

For example, with Customer 1 in 2016, we had 171 orders for the product "Bikes". But the potential number of orders in this year could have been 500. So, we missed out on 329 orders for this product. I would like to see values in new table, such as: 

Customer Name – Name of Category – Fact Number of Orders – Potential Number of Orders – Year

The "Name of Category" in this case - Number of orders. 

Similarly, regarding the number of products: in 2016, we only sold 1 product category – Bikes, but we know that the customer has a total of 3 product categories. So, we missed out on 2 products.

But for now, I'm getting something like this, the values ​​are not related to each other, and I can't add the customer.
I used: 

Combined =

VAR _a = SUMMARIZE ('Calendar Table','Calendar Table'[Year],"Name of Category", "Number of Products", "Fact", [Fact Number of Products],"Total",[Potential Number of Products])

VAR _b = SUMMARIZE ('Calendar Table','Calendar Table'[Year],"Name of Category", "Category", "Fact", [Fact Number of Orders],"Total",[Potential Number of Orders])

RETURN

UNION(_a,_b)

[v-tianyich-msft]

Hi @anastasiyaveko ,

I don't know what your custom looks like, so I disconnected them. I made simple samples and you can check the results below:

Real = var _t = ADDCOLUMNS('Internet Sales',"TOTAL",SUMX(FILTER(ALL('Internet Sales'),[Date]=EARLIER([Date])&&[Customer]=EARLIER([Customer])),[Number]))
RETURN MAXX(_t,[TOTAL])

Potential = var _t = ADDCOLUMNS('Potential Sales Table',"Total",SUMX(FILTER(ALL('Potential Sales Table'),[Product]=EARLIER([Product])&&[Year]=EARLIER([Year])),[Number]))
RETURN MAXX(_t,[Total])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[anastasiyaveko]

Hello! Thank you so much!
And I'm still can't understand how I can create new table with measures in it? 
Like this one?
Customer Group Product Real Potential Year
Customer 1 Number of Sales Bikes 171 500 2016
Customer 1 Number of Sales Bikes 202 500 2016
Customer 1 Number of Sales Accessories 100 2016
Customer 1 Number of Sales Components 400 2016
Customer 1 Number of Products 1 3 2016
Customer 1 Number of Products 1 1 2017

[v-tianyich-msft · Accepted solution]

Hi @anastasiyaveko ,

On the basis of the above, create the following table:

Table = ADDCOLUMNS('Internet Sales',"Potential",[Potential],"Real",[Real])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[v-tianyich-msft]

Hi @anastasiyaveko ,

Due to the policy, I am unable to download your sample, please provide the data in a table in text form.

Best regards,
Community Support Team_ Scott Chang

[anastasiyaveko]

Hello!
Added and also added all possible information. Hopes it will help.
Thank you