1
$\begingroup$

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

I want to obtain an equivalent of Table 3 in Power BI, which is something like this (disregarding the months ordered in alphabetical instead of chronological order): enter image description here

But when trying to do calculation on original 'Numerator' and 'Denominator' columns from Tables 1 and 2, respectively, I obtain the following: enter image description here

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.

$\endgroup$
2
  • $\begingroup$ Hi, welcome to Data Science SE! It seems to me that this specific question should rather be asked / searched on Stack Overflow. You will probably get an answer faster there. $\endgroup$ Jan 10, 2020 at 21:33
  • $\begingroup$ Hi @Romain, thank you for your helpful comment, it worked. I posted my question to Stack Overflow, and got an answer there. All the best! $\endgroup$
    – V_B
    Jan 16, 2020 at 4:57

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.