+ Reply to Thread
Results 1 to 1 of 1

Dax calculate and filter functions?

  1. #1
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    2013
    Posts
    26

    Question Dax calculate and filter functions?

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1