Getting Percentage Values for 2 Different Tables

[markefrody]

Hi,

I want to divide the values in the table "Count of Late Delivery" to the corresponding values in table "Count of All Delivery". 

Note: 

I am using Direct Query and the below 2 tables. I have chosen "Count" as values.

Table: Count of Late Delivery
Group - Jan - Feb - Mar

A         -  2   -   3   - 4 

B         -  3   -   6   - 8 
C         -  7   -  10  - 1

Table:  Count of All Delivery

Group - Jan - Feb - Mar

A        -  10  - 23   - 9 

B        -  6    - 8     - 10
C        - 10   - 11   - 12

For example:

"Group A" in "Jan" of "Table: Count of Late Delivery" / "Group B" in "Jan" of "Table: Count of All Delivery" 

I have tried the following formula but it does not correctly show the values:

Late Arrical % = DIVIDE (COUNT('Table: Count of Late Delivery), COUNT(Table:  Count of All Delivery))

Appreciate if you can help me out. Thanks!

[v-yuezhe-msft]

@markefrody，

Do you want to get “Group A in Jan of Table: Count of Late Delivery”/ "Group A in Jan of Table: Count of All Delivery"? If so, create relationship between the two tables, and create the following columns in your first table.

Janpercentage = DIVIDE(CountofLateDelivery[Jan],RELATED(CountofAllDelivery[Jan]))
Febpercentage = DIVIDE(CountofLateDelivery[Feb],RELATED(CountofAllDelivery[Feb]))
Marpercentage = DIVIDE(CountofLateDelivery[Mar],RELATED(CountofAllDelivery[Mar]))


However, if you want to get "Group A" in "Jan" of "Table: Count of Late Delivery" / "Group B" in "Jan" of "Table: Count of All Delivery", what is your expected result for Feb and Mar?

Regards,
Lydia

[markefrody]

Hi @v-yuezhe-msft,

Thank you for your suggestion. Have tried creating a relationship but it is telling me that I need to give unique values between the two columns. I cannot edit the values in the data source since I am using Direct Query. Please let me know if there is a work around for this without changing the values itself.

---

Not sure if I understood your question below:

Quote:
However, if you want to get "Group A" in "Jan" of "Table: Count of Late Delivery" / "Group B" in "Jan" of "Table: Count of All Delivery", what is your expected result for Feb and Mar?

:Unquote

I expect the results of the other months to be the percentage of the count of all delivery for that month only. If I misunderstood please let me know.

Best regards,
Mark

[v-yuezhe-msft]

@markefrody,

The tables you post in screenshot have different structure  as mine, could you please export the data to Excel of the tables and share me the Excel file? 

And please post expected result in table based on the sample data you post in your original post.

Regards,
Lydia

[markefrody]

@v-yuezhe-msft

You can view the file in link below. Data used is identical to what I am using. I have replaced and removed confidential details. But I believe that this data can suffice. Again, please note that I am using "DIRECT QUERY" via SQL to get data.
File (Please click here)

The below 3rd table highlighted in yellow is what I want to achieve. Have tried to divide the values directly but Power BI is giving me very far off values.

Please let me know if you need further information.

Best regards,
Mark

[v-yuezhe-msft · Accepted solution]

@markefrody,

As you are using DirectQuery mode, it is not possible to calculate the above expected result in Power BI Desktop, because in this case, we don't have option to create new table that contains Company, month and count of arrival, or create new table that contains unique values and then create relationship among the three tables.

In your scenario, I would recommend  you write SQL statement or create new table in SQL Server to drag Company, month and count of arrival for late and all arrival , then calculate percentage in Power BI Desktop.

Regards,
Lydia