Forum Discussion

markefrody's avatar
markefrody
Post Patron
8 years ago
Solved

Getting Percentage Values for 2 Different Tables

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!

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      markefrody
      Post Patron
      8 years ago

      Hi Anonymous,

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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