Calculating Sum and Average when duplicate records are in data

[Sachintha]

I have a data set that comes from a SharePoint list, which contains data regarding a 'ticket' system. The list contains following columns (simplified for sake of clarify; real list contains more columns).

Id, Dept, Type, Model, Start Date, Close Date

Type and Model are columns that allow multiple values (again, in reality, there are more columns that allow multiple values), so in my PowerQuery I expand those columns resulting in a final data set that has duplicates of records. Below is a sample data set.

Id,Dept,Type,Model,Start Date,Close Date
300,651,INS,A,6/26/2022,7/8/2022
301,651,INS,A,7/11/2022,7/22/2022
302,471,TRN,B,7/8/2022,7/30/2022
302,471,TRN,C,7/8/2022,7/30/2022
302,471,TRN,D,7/8/2022,7/30/2022
302,471,TRN,E,7/8/2022,7/30/2022
303,651,INS,A,7/25/2022,8/5/2022
304,485,ESC,F,8/7/2022,8/16/2022
304,485,SBL,F,8/7/2022,8/16/2022
305,485,ESC,F,8/7/2022,8/16/2022
305,485,SBL,F,8/7/2022,8/16/2022
308,552,ESC,G,7/31/2022,8/12/2022
309,485,ESC,F,8/7/2022,8/16/2022
309,485,SBL,F,8/7/2022,8/16/2022
318,741,SRV,H,9/2/2022,9/2/2022
320,741,INS,H,9/19/2022,9/23/2022
322,552,ESC,G,9/25/2022,9/30/2022
322,552,FOC,G,9/25/2022,9/30/2022
323,552,ESC,G,10/17/2022,10/21/2022
323,552,FOC,G,10/17/2022,10/21/2022
326,475,INS,I,8/11/2022,8/29/2022
327,485,ESC,F,8/25/2022,9/2/2022
327,485,SBL,F,8/25/2022,9/2/2022
329,485,INS,A,9/6/2022,9/15/2022
330,499,MOD,H,10/9/2022,10/21/2022
330,499,SBL,H,10/9/2022,10/21/2022
332,741,INS,J,9/7/2022,9/10/2022
333,554,SRV,I,9/7/2022,10/26/2022
337,558,MOD,K,10/10/2022,10/12/2022
337,558,SBL,K,10/10/2022,10/12/2022
338,485,INS,A,9/19/2022,9/23/2022
339,485,ESC,F,9/25/2022,9/30/2022
342,499,INS,H,11/7/2022,11/14/2022
345,741,FOC,H,11/1/2022,11/4/2022
345,741,SRV,H,11/1/2022,11/4/2022
345,741,TRN,H,11/1/2022,11/4/2022
346,499,SRV,L,10/9/2022,10/29/2022
346,499,SRV,F,10/9/2022,10/29/2022
347,474,INS,I,8/8/2022,9/28/2022
349,485,TRN,A,10/3/2022,10/7/2022
351,475,MOD,K,10/24/2022,10/26/2022
352,741,INS,K,9/26/2022,10/4/2022
353,499,SBL,J,10/1/2022,10/1/2022
354,475,ESC,I,10/6/2022,11/16/2022
354,475,INS,I,10/6/2022,11/16/2022
363,485,ESC,F,10/24/2022,11/3/2022
363,485,SBL,F,10/24/2022,11/3/2022
364,485,ESC,I,10/30/2022,11/4/2022
364,485,SBL,I,10/30/2022,11/4/2022
365,558,SBL,I,9/21/2022,9/23/2022
366,558,SBL,I,10/3/2022,10/3/2022
367,558,SBL,I,10/25/2022,10/25/2022
368,558,SBL,I,10/27/2022,10/27/2022
369,552,ESC,K,11/1/2022,11/28/2022
380,499,SBL,J,11/21/2022,11/23/2022
381,651,ESC,A,11/15/2022,11/18/2022
383,474,ESC,I,11/28/2022,12/7/2022
383,474,INS,I,11/28/2022,12/7/2022
384,499,SRV,J,11/22/2022,11/23/2022
384,499,SBL,J,11/22/2022,11/23/2022
385,485,ESC,F,11/28/2022,12/2/2022
385,485,SBL,F,11/28/2022,12/2/2022
389,485,ESC,F,12/5/2022,12/9/2022
389,485,SBL,F,12/5/2022,12/9/2022

As I mentioned, you can see multiple records such as ID=302, ID=304, etc. are duplicated, which is intentional.

Now, using this data set, I'd like to perform some calculatios regarding 'days open' of tickets, so, I calculate a column in DAX as follows:

Days Open = DATEDIFF(SampleData[Start Date], SampleData[Close Date], DAY) + 1

With this, I'd like to display a table in PowerBI which displays following columns.

Dept, # Tickets, Total Days Open, Avg. Days Open

Therefore, I calculated 3 measures to help with this:

Todal Days Open = SUM(SampleData[Days Open])
# Tickets = DISTINCTCOUNT(SampleData[Id])
Avg. Days Open = SUM(SampleData[Days Open]) / [# Tickets]

But, this doesn't quite work, since it sums up duplicate records all indiscriminately. For example, if we focus on just Dept=474, you see that there's only two tickets, but one of them is duplicated, so instead of Total Days = 52 + 10 = 62, I get 72 with 10 repeated twice. As such, my average calculation too is off.

How do I calculate Total Days Open correctly, so, in the above example, I get the figures Total Days = 62 and Average = 31?

[v-easonf-msft · Accepted solution]

Hi, @Sachintha 

Please add new measure like:

Total =
VAR tab =
    SUMMARIZE (
        SampleData,
        SampleData[Id],
        SampleData[Dept],
        "Total", AVERAGE ( SampleData[Days Open] )
    )
RETURN
    SUMX ( tab, [Total] )

Avg. Days Open2 = [Total] / [# Tickets]

Best Regards,
Community Support Team _ Eason