topic Re: Summarize Values To One Column Without Aggregation in Desktop

Summarize Values To One Column Without Aggregation

elior — Mon, 06 May 2024 09:31:35 GMT

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

Re: Summarize Values To One Column Without Aggregation

samratpbi — Mon, 06 May 2024 09:49:16 GMT

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!

Re: Summarize Values To One Column Without Aggregation

elior — Mon, 06 May 2024 10:03:06 GMT

I cannot unpivot it, im using it in direct query

Re: Summarize Values To One Column Without Aggregation

samratpbi — Mon, 06 May 2024 10:06:53 GMT

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!

Re: Summarize Values To One Column Without Aggregation

elior — Mon, 06 May 2024 10:09:24 GMT

maybe dax will be ok, by which formula ?

Re: Summarize Values To One Column Without Aggregation

samratpbi — Mon, 06 May 2024 10:12:27 GMT

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

Re: Summarize Values To One Column Without Aggregation

elior — Tue, 07 May 2024 08:31:11 GMT

the table is dynamic query.

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

Re: Summarize Values To One Column Without Aggregation

Anonymous — Mon, 13 May 2024 09:04:59 GMT

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.