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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Melissa_Connor
New Member

SUMIFS in PowerQuery

Hi Amazing Gurus

 

I am needing help to do a Sum IFS or similar in PowerQuery.

We receive applications throughout a school year to provide funding for students with specific health needs. There are a maximum number of hours/funding that we allocate per health need. Some Schools will submit multiple applications for the same student throughout the year which will eventually add up to exceed the maximum hours that we would allocate.

I need help to work out how much they have already been allocated, compared to their new application to see if they are eligible for any more hours/funding or not with their new application…… So far I have managed to collate all the applications in to one spreadsheet (love Power Query) for this. I have created a Master Spreadsheet which tracks the student’s applications, the date they applied, and hours allocated. I have been able to identify duplicates and flag them. I just can’t get my head around how to do the calculation to figure out the difference as it needs to look at so many columns and then apply a calculation:

 

Unique Identifier (being Student ID Code and Health Need) which flags it is a duplicate application

Original Application date (to check if it is an earlier application than the current one)

Hours already Allocated

Then calculate the difference they are entitled to if anything at all.

 

For example if Student X with Diabetes applied on 11/11/2020 and is receiving the Maximum allocation of 3.3hrs, then I want the new application that is received on 20/11/2020 to calculate to 0 as they are already receiving the maximum hours. You will see below (yellow highlighted row) that they are already flagged as duplicate, but the Hours allocated still calculates to 3.3, but it should be 0.

 

Alternatively if Student 12 with Diabetes is already receiving 1.6 hours allocation, then I want the new application that is received for student 12 to calculate to 1.7 as they are entitled to a maximum of 3.3 hours.

 

Example SUMIFS Formula required.PNG

Appreciate your help.

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Melissa_Connor 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

c1.png

 

You may create a new custom column with the following m codes.

= Table.AddColumn(#"Changed Type", "Result Allocated", each let 
date=[Date],
sname = [StudentName],
tab = Table.SelectRows(#"Changed Type",each [StudentName]=sname and [Date]<date)
in 
if Table.IsEmpty(tab)
then [Hours allocated]
else
  if List.Sum(tab[Hours allocated])>=3.3 
  then 0
  else 3.3-List.Sum(tab[Hours allocated]))

c2.png 

 

Result:

c3.png

 

Best Regards

Allan

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @Melissa_Connor 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

c1.png

 

You may create a new custom column with the following m codes.

= Table.AddColumn(#"Changed Type", "Result Allocated", each let 
date=[Date],
sname = [StudentName],
tab = Table.SelectRows(#"Changed Type",each [StudentName]=sname and [Date]<date)
in 
if Table.IsEmpty(tab)
then [Hours allocated]
else
  if List.Sum(tab[Hours allocated])>=3.3 
  then 0
  else 3.3-List.Sum(tab[Hours allocated]))

c2.png 

 

Result:

c3.png

 

Best Regards

Allan

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

Thank you! I had to modify this slightly to add an and but it worked perfectly, thank you so much!

CNENFRNL
Community Champion
Community Champion

Hi, @Melissa_Connor , in general, you might want to use Table.Group to obtain the sum of certain rows.

It's way much easier for others to keep up with you over your question with some mockup dataset and simple examples of desired results.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors