Happy Friday Everyone,
I am working in Excel 2016 and am new to the world of power pivot and DAX functions. I received help (thanks to Chicagolarsons and Mike) solving an analysis problem by using power pivot and DAX to determine the number of days between the first visit and the last, as well as the number of visits in between to calculate the average number of days between visits for each client. On the sample sheet (attached), it works perfectly. On my master data sheet (which is around 8600 rows, 631 distinct clients and 18 providers), not so much. Here's the issue:
Pic of Avg Freq Expanded.png
In the pic above, the formula works perfectly. Here, the clients are sorted by Status (DD,TID,T) and their average frequency of visits (days between) is correct. However, when I collapse the field to just show averages within each status, it becomes this:
Pic of Avg Freq Collapsed.png
The calculation becomes skewed. I suspect it is because the total number of days and visits on my master spreadsheet aggregated to 546 and 8,686 respectively, which is an average frequency of 0.1. I need the data disaggregated by client (as it appears in the first image), but I also need it to remain accurate (total avg freq/total # of clients in each status category) when it is collapsed to status.
I have been playing with DAX functions mixing CALCULATE, DISTINCT COUNT, FILTER all afternoon with no luck. Any ideas?
Thanks!
Lou
Bookmarks