Show average in total of matrix

[smparnon]

Hi,

So I'm trying to show the average of a datediff between two tables.  

Time between provider completed and pharmacist initiated =

DATEDIFF(provider[Latest_Event_Time],[Test Latest Pharmacist Event Time2],MINUTE)

Average Time Between Provider Completed and Pharmacist Initiated =

IF(

    HASONEVALUE('pharmacist dc initiated'[Clean_MRN_Pharmacy]),

    [Time between provider completed and pharmacist initiated],  

    DIVIDE(

        SUMX(

            VALUES('pharmacist dc initiated'[Clean_MRN_Pharmacy]),

            [Time between provider completed and pharmacist initiated]

        ),

        COUNTROWS(VALUES('pharmacist dc initiated'[Clean_MRN_Pharmacy])),

        BLANK()

    )

)

I also tried doing an averagex function with a values but that gave me the same problem of essentially summing the data.

The tables are in a many to many relationship.  They are joined on the clean_mrn_pharmacy and clean_mrn_provider.  There are multiple rows of the same latest_event_Time but only 1 row for each test_latest_pharmacist_event_time2.  I don't have a way to join the tables by a like index or identifier, just the same mrn, and there are more rows in the provider table than the 'pharmacist dc initiated' table.

My main problem is that the averages are showing sums in my tables.  It would be easier if I could make a datediff calculated column but the many to many relationship is preventing that, and when I use a lookupvalue it only gives a few values.  The times do seem to match up in hte visual when I put the providers event time and pharmacist time in a table, so there's something I am missing.  

Thank you!

[DataNinja777]

Hi @smparnon ,

The issue you are experiencing is likely due to the many-to-many relationship between your tables, which is affecting the aggregation when calculating the average. Since there are multiple provider event times but only one pharmacist event time per MRN, it's crucial to ensure the DATEDIFF function is operating on the correct row context.

First, modify the DATEDIFF calculation to ensure it operates on single values instead of multiple rows. You can achieve this using SELECTEDVALUE, which helps to extract a single event time per context.

Time between provider completed and pharmacist initiated =
VAR ProviderTime = SELECTEDVALUE(provider[Latest_Event_Time])
VAR PharmacistTime = SELECTEDVALUE('pharmacist dc initiated'[Test Latest Pharmacist Event Time2])
RETURN IF(NOT(ISBLANK(ProviderTime)) && NOT(ISBLANK(PharmacistTime)), DATEDIFF(ProviderTime, PharmacistTime, MINUTE))

Now, for the average calculation, your current approach is summing the values instead of computing a true average in the total row. Instead of using SUMX inside the DIVIDE function, using AVERAGEX on VALUES() ensures that the aggregation happens correctly.

Average Time Between Provider Completed and Pharmacist Initiated =
AVERAGEX(
    VALUES('pharmacist dc initiated'[Clean_MRN_Pharmacy]),
    [Time between provider completed and pharmacist initiated]
)

If the issue persists due to the many-to-many relationship, another approach is using SUMMARIZE to explicitly reshape the table before performing the average calculation. This method ensures that the calculation is performed at the correct granularity.

Average Time Between Provider Completed and Pharmacist Initiated =
AVERAGEX(
    SUMMARIZE(
        'pharmacist dc initiated',
        'pharmacist dc initiated'[Clean_MRN_Pharmacy],
        "Time_Diff", [Time between provider completed and pharmacist initiated]
    ),
    [Time_Diff]
)

Since your tables have a many-to-many relationship, an alternative fix is to use DISTINCT() inside SUMX to avoid double-counting MRN entries.

Average Time Between Provider Completed and Pharmacist Initiated =
DIVIDE(
    SUMX(
        DISTINCT('pharmacist dc initiated'[Clean_MRN_Pharmacy]),
        [Time between provider completed and pharmacist initiated]
    ),
    COUNTROWS(DISTINCT('pharmacist dc initiated'[Clean_MRN_Pharmacy]))
)

This ensures that each MRN is only counted once when computing the average. If none of these approaches fully resolve the issue, consider introducing a bridge table to mediate the many-to-many relationship and structure the data in a more controlled manner. Let me know if you need further refinements.

Best regards,

[smparnon]

Those didn't seem to give correct averages.  If I create a bridge table as 

Bridge_MRN = DISTINCT(UNION(VALUES('pharmacist dc initiated'[Clean_MRN_Pharmacy]), VALUES('provider'[Clean_MRN_Provider]))) it doesn't let me use related.  I've tried lookupvalue to no avail as well.