Forum Discussion
calculated column to aggregate based on two columns
Hi ,
I'm looking for some help in writing DAX to create a Calculated column with the below requirements -
Below table contains Customer, Department, Region and number of Orders.
I want to create the Calculated Column as shown below, which has the sum of Orders of each customer in each Region (ignoring the Deparment)
Total number of Orders = Group (customer) + Group (Region)
| Customer | Department | Region | Orders | CALCULATED COLUMN |
| AAA | Sales | Asia | 0 | 2 |
| AAA | Marketing | Asia | 2 | 2 |
| AAA | R&D | Europe | 5 | 5 |
| BBB | Sales | Americas | 0 | 0 |
| BBB | Procurement | Americas | 0 | 0 |
| BBB | Research | Europe | 3 | 3 |
| CCC | Sales | Americas | 5 | 11 |
| CCC | Marketing | Americas | 6 | 11 |
| CCC | HR | Asia | 10 | 24 |
| CCC | Finance | Asia | 14 | 24 |
Appreciate any help on writing the DAX for this, thank you.
Hello hnb_tor ,
Please try the below formula its more simpler..
CALCULATED COLUMN =
CALCULATE(SUM('Table'[Orders]),ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Region]))If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
7 Replies
Hello hnb_tor ,
Please try the below formula its more simpler..
CALCULATED COLUMN =
CALCULATE(SUM('Table'[Orders]),ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Region]))If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
thanks so much, this solution worked!
Hi,
Please check the below picture and the attached pbix file.
One of ways to achieve this is using WINDOW DAX Function.
WINDOW function (DAX) - DAX | Microsoft Learn
Expected result CC = SUMX ( WINDOW ( 1, ABS, -1, ABS, Data, , , PARTITIONBY ( Data[Customer], Data[Region] ), MATCHBY ( Data[Customer], Data[Department], Data[Region] ) ), Data[Orders] )Hi,
Thank you for looking into this request. I'm trying to follow the DAX example, however I see an error "Failed to resolve name 'MATCHBY', it is not a valid table, variable, or function name"
Could it be that the PowerBI desktop version is old?Hi,
Thank you for your message, and another option to achieve this is writing DAX formula for calculated column something like below. Please also check the attache pbix file.
I am not sure how you wrote DAX formula in your sample on your computer, but please check if MATCHBY dax function works in my sample on your computer.
Expected result CC = /* SUMX ( WINDOW ( 1, ABS, -1, ABS, Data, , , PARTITIONBY ( Data[Customer], Data[Region] ), MATCHBY ( Data[Customer], Data[Department], Data[Region] ) ), Data[Orders] ) */ VAR _currentrowcustomer = Data[Customer] VAR _currentrowregion = Data[Region] RETURN SUMX ( FILTER ( Data, Data[Customer] = _currentrowcustomer && Data[Region] = _currentrowregion ), Data[Orders] )
