cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dti Frequent Visitor
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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: SUMIF Equivalent in Power BI

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.
12 REPLIES 12
Super User
Super User

Re: SUMIF Equivalent in Power BI

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



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

Proud to be a Datanaut!




dti Frequent Visitor
Frequent Visitor

Re: SUMIF Equivalent in Power BI

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

Super User
Super User

Re: SUMIF Equivalent in Power BI

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



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

Proud to be a Datanaut!




dti Frequent Visitor
Frequent Visitor

Re: SUMIF Equivalent in Power BI

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

Super User
Super User

Re: SUMIF Equivalent in Power BI

You are using Direct query over ssas?


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

Proud to be a Datanaut!




dti Frequent Visitor
Frequent Visitor

Re: SUMIF Equivalent in Power BI

Yes, that is correct.

Super User
Super User

Re: SUMIF Equivalent in Power BI

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


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

Proud to be a Datanaut!




Highlighted
dti Frequent Visitor
Frequent Visitor

Re: SUMIF Equivalent in Power BI

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.

Super User
Super User

Re: SUMIF Equivalent in Power BI

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


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

Proud to be a Datanaut!




Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 170 members 1,694 guests
Please welcome our newest community members: