@Khushidesai0109 The first two calculations achieved the same output as my SUMMARIZE functions so the counts are correct. However, the DIVIDE function is still not producing the right % by Case Owner.
And for Case Owners that don't have a row in that month for the OverAP table a % isn't generated, but rather the # Total # of OverAPs is listed for them instead. This is the output for the first few Case Owners when I add PercentageOverdueAPs and Case Owner as fields to a Table visual without Months:
Case Owner A has 301 APs overdue total and 1215 cases = 24.7%
Case Owner B has 0 APs overdue total and 1 case = 0%
Case Owner C has 709 APs overdue total and 1191 cases = 59.5%
PercentageOverdueAPs Case Owner
11.76 A
14,290.00 B
12.00 C
(14290 is the total # APs overdue)
If I add in the date it just lists the same amount for every month.
These are the relationships.
Hi,@JJL_Ptbo
First of all, in my understanding your requirement is to count case totals by Participant ID in Table 1, then count the combined number of people belonging to them in Table 2, and divide the two numbers to get the percentage. I think the reason why you have added the filter, the percent value is not correct is that you count the number of rows is not divided by the person who belongs to it, but you added the filter, he will only sift the data, you count is still the total of the whole table. If my understanding is wrong, please give me your relative opinion:
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.To meet your needs, my measure are as follows:
Percentage divided =
VAR countbyowener=CALCULATE(COUNTROWS('All Months Case Details'),FILTER(ALLSELECTED('All Months Case Details'),'All Months Case Details'[Participant ID]=MAX('All Months Case Details'[Participant ID])))
VAR conuntbyap=CALCULATE(COUNTROWS('AP Summary'),FILTER(ALLSELECTED('AP Summary'),'AP Summary'[Case Owner]=MAX('AP Summary'[Case Owner])))
RETURN
DIVIDE(countbyowener,conuntbyap,0)
3.You can also modify the percentages if you wish to implement them as I have shown below:
4.Here's my final result, which I hope meets your requirements.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-linyulu-msft This gets me closer, but it still isn't producing the correct %.
The # of Cases isn't by Participant ID the Participant ID represents the Case. And an AP is a requirement to have completed with a case and this metric is given as a total by that Case Owner in the table as opposed to by Participant/Case. I don't see an option to attach files, but this is sample in Power BI. In my real file there is a date table and an employee table the Case Owner field is Table 1 and 2 have relationships to the Employee table and Table 1 and 2 have relationships to the Date table.
All Months Case Details
AP Summary
Top Chart counting the # of cases with filters for Month and Case Owner. Bottom chart displaying # of APs overdue by Case Owner in December. The % of APs overdue for Case Owner A out of their total # cases in December should then be 50%.
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
