Power BI Desktop is a free data modelling tool available from Microsoft. It uses DAX (Data Analysis Expressions) so that reports can perform calculations and apply formula. DAX may sound intimating at first, but the basics can be easy to understand if you are familiar with Excel functions. DAX is not a programming language, it’s a formula language used to define custom calculations, and is particularly important for performing calculations based on dates.
At Smartr365, we have created common measures which brokerages indicated they would commonly report on. You may want to augment these, if you wish to create custom logic which is not covered by the measures we have created. For most adhoc reporting, you can create simple tables which do not require this.
In this section we will provide a simple definition of what each DAX Measure does and how you can use it.
We strongly suggest reading our ‘How to create a custom report in Power BI using Smartr365 Mortgage data?’ guide first.
Date Filters
These are useful when you want to filter by one or more of the many types of date (see date glossary at end of document) but you don't want to be limited to reports using one of our special Fee Total by Date Calculations (see fees section below).
One example might be when you want to show all cases in a Meeting Status within a date range (for example the first week of June) which may or may not have a Proc Fee specified:
• In this case, just drag on the /Date Filters/Status Dates/Is Meeting Between Selected Dates calculation.
• Then filter that column in the table to only show 'Yes' values. This filters the rows for you.
• Then drag on the non-filtering version of the Proc Fee calculation, from /Fees/No Date Specified/Total Proc Fee .
Fees
We have divided the Fees Calculations into three types depending on how you want to filter the results.
At first glance this might seem a little complicated. If I want to know the Total Client Fee paid in June, then surely I should be able to just drop on Client Fees and set the date range?
But do you mean the total client fees for cases which were added in June, Submitted in June, Completed in June etc. ?
When filtering by date we need to specify which date we are reporting on.
In Power BI we do this by using DAX Measures, which is a lot like a Formula in Excel except that they also allow you set a filter or relationship to use for that calculation.
Below, we discuss each of the fee measures Smartr365 has provided in the templates. This should help you understand which DAX Measure to use, based on what type of reporting you are generating.
Fees/By Payment Dates
These measures calculate the totals for each type of fee but filtered by the date each of those fees where paid.
• Total Client Fees is filtered by the Client Fee Paid Date column.
• Total Solicitor Fees is filtered by the Solicitor Fee Paid Date column.
• Total Proc Fees is filtered by the Proc Fee Paid Date column.
• Total Misc Fees is filtered by the Misc Fee Paid Date column.
• The interesting one is Total Fees Paid because it is the sum of all four of the fee columns, each of which filters by it’s own particular date paid column. This would be very tricky to replicate in excel and shows the power of using Power BI and DAX measures!
Fees/By Status Dates
Totals for each type of fee but filtered by either the date the case was Submitted or the date it was Completed.
Fees/No Date Specified
Totals for Client, Miscellaneous, Proc and Solicitor Fees as well as Total Fees (of all types) but with no automatic date filtering.
If a Fee of any of these types are in the system then they will be shown, regardless of the Date filter on the page.
These are intended to be used in conjunction with measures from the Date Filters section to filter by dates.
Also note that just because these all start with the word Total, that there could still be used when viewing individual cases. This is similar to Excel where you can use SUM function to total multiple rows but it doesn't care if there is only one row being totalled.
Note:
Finally, an extra note about Total Proc Fees:
Client Fees, Solicitor Fees and Miscellaneous Fees are really just like using SUM in excel. Proc Fees is different because as well as summing the proc fees for the cases, it also takes into account Proc Fee Splits by subtracting Fees split to other advisers and then adding fee splits from other advisers. However, if we are *not* totalling by adviser (say we have added the CaseId column to the report so we are showing each individual case) then these will cancel each other out and it is exactly the same as just summing the total proc fees for the case.
Number of Cases
Like the others except rather than fee totals these measures just count the total number of cases
Special
There is just one measure here which is used when drawing the heading in the introducer drill-through reports to get the selected introducer (if there is a single introducer selected).