Summarize Values To One Column Without Aggregation

[elior]

Hi,

I have the following tables that I want to add from Table 1 the total summarized values From Table 2

Table1 - number of units bought each hour

Table2 - number of items each buyer bought

when im trying to bring to table1 the number of units that was bought in total for each buyer from table2, im getting Aggregation for every hour instead of just one column that will contain the sum items.

the visual is Matrix

how can I get the "Want To Get:" table that attached instead of "Result:" ?

thank you

Table 1:(Units)

Buyer	6	7	8	9	10	Boxes
Alex	10	5	6	1	8	?
Adam	20	5	6	2	9	?
Ben	18	10	41	40	5	?
Dan	60	20	14	12	12	?
Jim	22	15	15	54	3	?

Table2: (items)

Buyer	6	7	8	9	10
Alex	1	1	1	1	1
Adam	3	5	4	2	1
Ben	6	8	1	5	1
Dan	5	5	2	4	5
Jim	8	3	3	2	1

Result:

Buyer	6	Items	7	Items	8	Items	9	Items	10	Items
Alex	10	1	5	1	6	1	1	1	8	1
Adam	20	3	5	5	6	4	2	2	9	1
Ben	18	6	10	8	41	1	40	5	5	1
Dan	60	5	20	5	14	2	12	4	12	5
Jim	22	8	15	3	15	3	54	2	3	1

Want To Get:

Buyer	6	7	8	9	10	Items
Alex	10	5	6	1	8	5
Adam	20	5	6	2	9	15
Ben	18	10	41	40	5	21
Dan	60	20	14	12	12	21
Jim	22	15	15	54	3	17

[Anonymous]

Thank you @samratpbi   for your prompt reply.

Hi @elior  ,
May I ask if your problem has been solved? If the problem has not yet been solved, please feel free to ask us a question. Would you be able to provide more data (without private information) so that we can better understand and solve the problem you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[elior]

I cannot unpivot it, im using it in direct query

[samratpbi]

Hi, if you cant use Power Query, then instead of the transformation, you can do sum of all the columns from Table 2 using DAX.

If this helps o resolve your problem, then please mark it as solution, Thanks!

[elior]

maybe dax will be ok, by which formula ?

[samratpbi]

sum of all the columns from table 2, i.e. SUM(T2[7]) + SUM(T2[8])...... so on. where I assume 7,8... are the column names in 2nd table T2

[elior]

the table is dynamic query.

i tried it, but with the same result - aggregation by hour.

[samratpbi]

Hi, In power query, for table 2, you need to select Buyer column and unpivot other columns. That will give you 3 columns, Buyer, Attribute (which is the items) and values.

Then you join the 2 tables based on Bauyer. Better to have Buyer dimension table and join based on Buyer name.

Also create a simple measure like Items value = SUM(T2[value]) (i.e. sum of the value column from the unpivoted table

Then in a table visual, bring Bauyer from the dimension table, all the columns from table 1, then the new measure which was created on table 2..

If this helps to resolve your problem, then please mark it as solumn provided, Thanks!