Grouping a sum based on few columns, and allocating the result to other, related column

[hcn]

Hi,

I cannot figure out how to do the following in a DAX measure. Please note, that I am looking for a way to do this in a measure, not as a calculated column. Here is my data example, how the result should be visible in the reporting.

What I want to do, is to group the sum of Amt column based on country, supplier, and brand, and then allocate the result to products.

The data:

Country	Supplier	Brand	Product	Amt
C1	S1	B1	P1	0
C1	S1	B1	P2	100
C2	S2	B1	P3	40
C2	S2	B1	P4	20

Desired result:

Country Supplier Brand Product Amt Amt grouped by country, supplier and brand

Country	Supplier	Brand	Product	Amt	Amt grouped by country, supplier, and brand
C1	S1	B1	P1	0	100
C1	S1	B1	P2	100	100
C2	S2	B1	P3	40	60
C2	S2	B1	P4	20	60

More specify from the product perspective:

Product	Amt grouped by country, supplier and brand
P1	100
P2	100
P3	60
P4	60

Please do not get confused if the attribute examples don't seem too logical, I replaced the attributes from my original dataset with just some random attribute names.

This would be so easy to with SQL, but I don't seem to understand how to do this with DAX.

Thanks for your help!

[amitchandak]

@hcn ,

You can have a measure like

calculate(sum(Table[Amt]), allexcept(Table,Table[country],Table[supplier],Table[brand]))

or a column like

sumx(filter(Table,Table[country] =earlier(Table[country]) && Table[supplier]=earlier(Table[supplier]) && Table[brand]=earlier(Table[brand])),Table[Amt])

[hcn]

Hi,

Thanks for you fast reply. Unfortunately your solution did not work as expected.

Thanks anyway!