Calculating % by Dividing Filtered Matrix Columns in MS Power BI

Question

Given: A monthly percentage (%) metric has to be calculated from dividing a column ('Numerator') from one table by a column ('Denominator') from another table, both filtered by month, as given in an example below:

Table 1:

Date_1   Numerator
01-Jan-19   5
05-Feb-19   4
04-Apr-19   1
07-May-19   3
11-Jun-19   5
22-Jun-19   4
25-Jul-19   5
31-Aug-19   1
03-Sep-19   4
25-Oct-19   5

Table 2:

Date_2   Denominator
03-Jan-19   7
05-Jan-19   9
16-Feb-19   8
22-Feb-19   7
04-Mar-19   10
18-Mar-19   8
24-Apr-19   8
25-Apr-19   8
01-May-19   10
10-May-19   8
04-Jun-19   8
08-Jun-19   7
03-Jul-19   8
18-Jul-19   6
23-Aug-19   10
31-Aug-19   9
03-Sep-19   9
20-Sep-19   7
03-Oct-19   7
27-Oct-19   6

One way this task can be solved is by using pivot tables in MS Excel by dividing the rows from 'Numerator' by rows from 'Denominator' corresponding to the same months in filtered 'Date_1' and 'Date_2' columns, and then formatting the results as %, as shown in Table 3 below:

Table 3:

Month_1   Sum of Numerator     Month_2   Sum of Denominator   Month   %             
Jan       5                Jan       16                       Jan   31.25%
Feb       4                Feb       15                       Feb   26.67%
Mar                        Mar       18                       Mar   0.00%
Apr       1                Apr       16                       Apr   6.25%
May       3                May       18                       May   16.67%
Jun       9                Jun       15                       Jun   60.00%
Jul       5                Jul       14                       Jul   35.71%
Aug       1                Aug       19                       Aug   5.26%
Sep       4                Sep       16                       Sep   25.00%
Oct       5                Oct       13                       Oct   38.46%
Nov                        Nov                                Nov   
Dec                        Dec                                Dec                   
Grand Total 37             Grand Total  160             Grand Total 23.13%

MS Power BI is used for querying the data automatically from the database, but the problem arises with division of pivot tables (which are represented as matrices in Power BI).

Question: How can one obtain an equivalent output to 'Month' and '%' columns from Table 3 in Power BI using the equivalent of Tables 1 and 2? I am new to Power BI and and am not sure of what the problem is and how to correct it.

RADO RADO · Accepted Answer · 2020-01-13T21:37:59

You need to create a Calendar Table, and connect it to both tables.

For example, create a calendar (Date) table like this:

Then connect it to your tables using date fields:

Create 3 simple DAX measures:

Sum Nominator = SUM(Table1[Nominator])

Sum Denominator = SUM(Table2[Denominator])

Total % = DIVIDE( [Sum Nominator], [Sum Denominator])

Drop Month into a pivot, and then add 3 measures:

Calculating % by Dividing Filtered Matrix Columns in MS Power BI

1 Answers