Forum Discussion
calculated column to aggregate based on two columns
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
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?
Super UserHi,
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.