Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
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
Proud to be a Super User!
Check out my blog: Power BI em Português@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
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, that is correct.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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".
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |