Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
hcn
New Member

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

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!

2 REPLIES 2
amitchandak
Super User
Super User

@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])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi,

 

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

 

Thanks anyway!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.