calculated column to aggregate based on two columns

[hnb_tor]

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. 

[dharmendars007 · Accepted 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

LinkedIN 

[hnb_tor]

thanks so much, this solution worked! 

[Jihwan_Kim]

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

[hnb_tor]

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? 

[Jihwan_Kim]

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

[hnb_tor]

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, 

[hnb_tor]

Sorry, I havent yet upgraded the version, will upgrade and test soon.