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.

[v-bmanikante]

Hi @smparnon ,

Thank you for reaching out to Microsoft Fabric Community Forum.

@DataNinja777 Thank you for your quick reply.

@smparnon Thanks for the update! You're absolutely right , the main challenge here is the many-to-many relationship between the provider and pharmacist tables, combined with the absence of a reliable 1:1 mapping. This makes it difficult to perform accurate row-level calculations such as DATEDIFF.

Please try a few workarounds mentioned below:

1. Please try to create a more Granular Bridge (If Possible):

If you are able to create a unique identifier (e.g., a combination of MRN + timestamp, or an encounter ID), that would be the ideal solution. You could generate this either upstream in the source system or within Power Query using a row number per MRN or similar logic.

2. If a unique ID isn't feasible, you can match each pharmacist event to the closest prior provider event using this DAX:

Time between provider completed and pharmacist initiated =
VAR MRN = SELECTEDVALUE('pharmacist dc initiated'[Clean_MRN_Pharmacy])
VAR PharmacistTime = SELECTEDVALUE('pharmacist dc initiated'[Test Latest Pharmacist Event Time2])
VAR ClosestProviderTime =
    CALCULATE(
        MAX(provider[Latest_Event_Time]),
        FILTER(
            provider,
            provider[Clean_MRN_Provider] = MRN &&
            provider[Latest_Event_Time] <= PharmacistTime
        )
    )
RETURN
    IF(
        NOT(ISBLANK(ClosestProviderTime)) && NOT(ISBLANK(PharmacistTime)),
        DATEDIFF(ClosestProviderTime, PharmacistTime, MINUTE)
    )

3.Then try to calculate the average:

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

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @smparnon ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy
 

[v-bmanikante]

Hi @smparnon 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @smparnon ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy