Forum Discussion

MuthalibAbdul's avatar
MuthalibAbdul
Icon for Helper II rankHelper II
1 year ago
Solved

Calculate percentage between two tables

So I need to calculate the return to hospital percentage when we see a patient and when we do not see a patient to compare (and prove that if we have seen the patient, our RTH is low which means good)

 

I have table1 where all the data is compiled. However I am interested only in 5 specific facilities  (my data has 200+ facilities) and I am interested in only 2024 september (my data has 3 years worth data)

Table2 has the patients that returned to hospital and if they were seen by us or no. 

 

Table1

 

Table2

 

Measures created

I have all measures created in one table

Here I am calculating if they were consulted by us or no. I want to use these numbers as the numerator where calculating return to hospital 

 

Next I need to figure out measures for the other table where it would take only september and only specific location within the location column. Both these tables have a many to many relationship. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi MuthalibAbdul ,

     

    Thanks for the reply from Ashish_Mathur .

     

    You are trying to calculate the percentage of patients Consulted as Yes and No in Table2 for comparison based on the location and date filtered in Table1 right?

     

    This is the data I created to test it:

     

    Table1:

     

    Table2:

     

    Make sure there is no relationship between these two tables.

     

    Please create a measure to count the number of ID Consulted = “Yes” when I select five specific locations and specific dates in Table1.

     

    YesCount =
VAR _Slicer =
    VALUES ( 'Table1'[Location] )
VAR _outcome =
    COUNTROWS (
        FILTER (
            'Table2',
            'Table2'[Location]
                IN _Slicer
                    && 'Table2'[Discharge Date] >= MIN ( 'Table1'[Date of Service] )
                    && 'Table2'[Discharge Date] <= MAX ( 'Table1'[Date of Service] )
                    && 'Table2'[ID Consulted] = "Yes"
        )
    )
RETURN
    _outcome

     

     

    Please create two measures to calculate the percentage of the total when the ID consulted is listed as Yes and No, respectively.

     

    RTH_Percentage_Consulted = DIVIDE([YesCount],COUNTROWS('Table1'))
    RTH_Percentage_NotConsulted = DIVIDE(COUNTROWS('Table1') - [YesCount],COUNTROWS('Table1'))

     

     

    Use a column chart to compare the two percentages.

     

    In my sample, you can see that the calculation is correct.

     

    If there is an error in your data, I would like you to provide a Power BI Desktop file (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set to public access), SharePoint, or a Github repository and then share the URL of the file.

     

    The pbix file is attached.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi MuthalibAbdul ,

     

    Thanks for the reply from Ashish_Mathur .

     

    You are trying to calculate the percentage of patients Consulted as Yes and No in Table2 for comparison based on the location and date filtered in Table1 right?

     

    This is the data I created to test it:

     

    Table1:

     

    Table2:

     

    Make sure there is no relationship between these two tables.

     

    Please create a measure to count the number of ID Consulted = “Yes” when I select five specific locations and specific dates in Table1.

     

    YesCount =
VAR _Slicer =
    VALUES ( 'Table1'[Location] )
VAR _outcome =
    COUNTROWS (
        FILTER (
            'Table2',
            'Table2'[Location]
                IN _Slicer
                    && 'Table2'[Discharge Date] >= MIN ( 'Table1'[Date of Service] )
                    && 'Table2'[Discharge Date] <= MAX ( 'Table1'[Date of Service] )
                    && 'Table2'[ID Consulted] = "Yes"
        )
    )
RETURN
    _outcome

     

     

    Please create two measures to calculate the percentage of the total when the ID consulted is listed as Yes and No, respectively.

     

    RTH_Percentage_Consulted = DIVIDE([YesCount],COUNTROWS('Table1'))
    RTH_Percentage_NotConsulted = DIVIDE(COUNTROWS('Table1') - [YesCount],COUNTROWS('Table1'))

     

     

    Use a column chart to compare the two percentages.

     

    In my sample, you can see that the calculation is correct.

     

    If there is an error in your data, I would like you to provide a Power BI Desktop file (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set to public access), SharePoint, or a Github repository and then share the URL of the file.

     

    The pbix file is attached.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file or share the download link of the PBI file.