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
dti
Frequent Visitor

SUMIF Equivalent in Power BI

I have two unrelated tables that I am trying to perform a simple SUMIF calcluation on. I can easily do this in Microsoft Excel but am struggling to get it completed in Power BI. The simplified scenario is described below. Both tables in my Power BI model are loaded using DirectQuery.

 

Table1: Sales Summary

     Column B: Equivalent Payroll Finish Date

     Column C: Revenue

 

Table2: Payroll

     Column B: Payroll Finish Date

     Column C: Payroll Amount

     Column E (Calculated): Same Finish Date Revenue

 

In 'Table2 [Column E]' I'm trying to calculate the total revenue for the same payroll finish date listed in Table1. This is easily completed in excel using the following formula: "=SUMIF('Sales Summary'!$B$3:$B$7,Payroll!B3,'Sales Summary'!$C$3:$C$7)"

 

How could I go about completing this in Power BI? I can't get the "CALCULATE(SUM..." function to filter properly.

 

Thank you for any help as I've been working on this for a couple days now!

 

Table 1 Example.png

 

 Table 2 Example.png

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @dti,

Create a calendar table in the current database of SQL Server which you get data from following the guide in this blog, then import the calendar table to your current Power BI Desktop file, and link the calendar table to Sales summary table and Payroll table using Datekey.


This way, you will be able to get your desired result after creating a table visual by using datekey of calendar table and Payroll Amount solumn of Payroll table and Revenue of Sales summary table .

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
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

12 REPLIES 12
v-yuezhe-msft
Employee
Employee

Hi @dti,

Create a calendar table in the current database of SQL Server which you get data from following the guide in this blog, then import the calendar table to your current Power BI Desktop file, and link the calendar table to Sales summary table and Payroll table using Datekey.


This way, you will be able to get your desired result after creating a table visual by using datekey of calendar table and Payroll Amount solumn of Payroll table and Revenue of Sales summary table .

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MFelix
Super User
Super User

HI @dti,

 

If you link the two tables by the Date (assuming the in the Payroll finish date there aren't any repeated items) you can them make the calculations directly in the visuals without the need of any formulas.

 

Regards,

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



dti
Frequent Visitor

@MFelix: Thanks for the suggestion. Unfortunately I am not able to link the two tables as there are repeated items.

Add a calendar table and then link it to your two tables, and have the final result.

 

Date = ADDCOLUMNS (
CALENDAR ( "2017-01-01"; "2020-12-31" );
"Year"; FORMAT ( [Date]; "YYYY" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"MonthNum"; FORMAT ( [Date]; "MM" );
"Month"; FORMAT ( [Date]; "MMM" );
"Weekday"; FORMAT ( [Date]; "ddd" );
"WeekdayNum"; WEEKDAY ( [Date]; 2 );
"Day"; FORMAT ( [Date]; "dd" );
"Cal Month"; FORMAT ( [Date]; "YYYY-MM" );
"Cal Quarter"; FORMAT ( [Date]; "YYYY" ) & "-Q"
& FORMAT ( [Date]; "Q" )) 

 

DAX Code from @dexterz,

 

 

 

 

Regards

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



dti
Frequent Visitor

@MFelix Thank you for this suggestion. My report is set to DirectQuery currently and it would not be suitable for me to switch it to import. (Therefore, I'm unable to add a calendar table because it requires that I switch to import mode). I am querying on a large amount of historical data in a SQL database. Any other ideas?

 

It baffles me that something so easy to do in Excel has stumped me for such a long time.

You are using Direct query over ssas?

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



dti
Frequent Visitor

Yes, that is correct.

If that the case you cannot do anything with the information in direct query since you cannot add or change, columns, measures, tables.

Probably you have to try and present the information in a different way using two visuals to get what you need.

Regards

Mfelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



dti
Frequent Visitor

I have been able to add columns, change columns, create measures, and calculated columns, etc. in DirectQuery mode.

 

 

One of my limitations is that DirectQuery does not allow transformations on DateTime types of data.

 

It sounds like I may have to go back to the drawing board completely. Thank you for your suggestions.

The SSAS models that i used don't allow to make changes but can be a limitation of my models.

Try to add the table as I said in the table view and not on the query view that will allow you to keep the direct query.

Regards

Mfelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



dti
Frequent Visitor

I do not believe you can access Table view in DirectQuery mode. At least it is not available on my Power BI desktop. My only options on the left hand tool bar are "report" and "relationship".

The table view is in the report not the direct query.

The formula i send to you is DAX in the query is M.

Mfelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.