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

Join 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.

Reply
Unimatrix8472
New Member

Excel to DAX: converting SUMIFS structured references ([column1],[@[column1])

Greetings!

 

I am trying to transition an excel workboook with several types of calculations to Power BI.  In particular, I am struggling with how to replicate a conditional SUMIFS calculation that uses structured references.  The excel expression is: =SUMIFS([Column1],[Column2],[@Column2],[Column3],[@[Column3]])

 

In DAX, how can I sum the values of column 1 given specific row values in columns 2 and 3?

 

I realize that the DAX framework is based on table/column/row refernece and not cell references (like excel), but I'm not making the intellectual leap here.  Any help would be appreciated!

1 ACCEPTED SOLUTION

@Unimatrix8472  you misplaced a parenthesus

allexcept('Raw Course Success Data - AY','Raw Course Success Data - AY'[Department], 'Raw Course Success Data - AY'[Year Term])

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

@Unimatrix8472  can you provide sample data and expected output?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Sure, here is a simplifed illustration.

 

Excel Expression:

Calculated Values =SUMIFS([Course Enrollment],[Department],[@Department],[Year Term],[@[Year Term]])

The expression calculates the enrollment sum across courses for a given department in a given year.  I am trying to accomplish the same with DAX in Power BI.

 

Example: Department A in Fall 2021 saw a total enrollment of 550 students (200+300+50), whereas Department E in Spring 2020 saw a total enrollment of 151 (74+77).

Unimatrix8472_0-1636064096130.png

Does this help?

@Unimatrix8472  try this measure

sumifEquivalent=calculate(sum(tbl[courseEnrollment]), allexcept(tbl,tbl[Department]),tbl[YearTerm]))

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Here's the implementation of your suggestion with my actual DAX:

Dept Enrl Per AY = calculate(sum('Raw Course Success Data - AY'[Course Enrollment]), allexcept('Raw Course Success Data - AY','Raw Course Success Data - AY'[Department]),'Raw Course Success Data - AY'[Year Term])
 
The following error occured: Cannot covert 'Summer 2016' of type Text to type True/False.
 
Summer 2016 is a [Year Term] value, like Fall 2021 or Spring 2020 in the example I provided in my previous reply.
 
Suggestions?

@Unimatrix8472  you misplaced a parenthesus

allexcept('Raw Course Success Data - AY','Raw Course Success Data - AY'[Department], 'Raw Course Success Data - AY'[Year Term])

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Oops.  Yes, this appears to have worked. Thank you very much!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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