Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to Solution.
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
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]
)
Hi, Thanks again for helping on this request.
I cant open the pbix file you have shared, there is a message that my PowerBI Desktop version is not compatiable with some of the features on this file.
I'm going to see if I can get PowerBI desktop client upgraded on my computer and test if I can try your solution. Thanks,
Sorry, I havent yet upgraded the version, will upgrade and test soon.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |