Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
Solved! Go to 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])
@Unimatrix8472 can you provide sample data and expected output?
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).
Does this help?
@Unimatrix8472 try this measure
sumifEquivalent=calculate(sum(tbl[courseEnrollment]), allexcept(tbl,tbl[Department]),tbl[YearTerm]))
Here's the implementation of your suggestion with my actual DAX:
@Unimatrix8472 you misplaced a parenthesus
allexcept('Raw Course Success Data - AY','Raw Course Success Data - AY'[Department], 'Raw Course Success Data - AY'[Year Term])
Oops. Yes, this appears to have worked. Thank you very much!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
8 |