Forum Discussion

hnb_tor's avatar
hnb_tor
Frequent Visitor
2 years ago
Solved

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 ...
  • dharmendars007's avatar
    dharmendars007
    1 year ago

    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 

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

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's avatar
hnb_tor
Frequent Visitor
2 years ago

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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      hnb_tor
      Frequent Visitor
      2 years ago

      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's avatar
        hnb_tor
        Frequent Visitor
        1 year ago

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