Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
igreeinf
New Member

Sum Column with Multiple Criteria

Hi All, 

 

Trying to acheive something like this in "New Column", using a DAX function. 

 

I want to sum the Sales values for matching Year, Month, and Cat1 rows. 

YearMonthCat1Cat2SalesNew Column
2019FebQuickFizz13
2019FebQuickBuzz23
2019FebBrownFizz37
2019FebBrownBuzz47
2019MarchQuickFizz511
2019MarchQuickBuzz611
2019MarchBrownFizz715
2019MarchBrownBuzz815

 

Thanks!

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @igreeinf ,

You also could try this calculated column.

Column =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Month] = EARLIER ( 'Table'[Month] )
            && 'Table'[Cat1] = EARLIER ( 'Table'[Cat1] )
    )
)

Here is the output.

Capture.PNG

Hope this can help you.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @igreeinf ,

You also could try this calculated column.

Column =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Month] = EARLIER ( 'Table'[Month] )
            && 'Table'[Cat1] = EARLIER ( 'Table'[Cat1] )
    )
)

Here is the output.

Capture.PNG

Hope this can help you.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
az38
Community Champion
Community Champion

Hi @igreeinf 

try new measure

Measure = calculate(sum(Tbl[Sales]);ALLEXCEPT(Tbl;Tbl[Year];Tbl[Month];Tbl[Cat1]))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Thanks @az38! I tried that and it worked before, but it's giving me values for Sales that don't exist at all, which is strange. 

 

Any insight?

az38
Community Champion
Community Champion

@igreeinf 

i think, this is the easiest case of dax-usage

what kind of incorrect values it's giving you? could you show an example to try to debug? maybe some filters?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.