How to calculate average time with condition in "New Measure" based on the date filter

[ekramulmostaqui]

Hi,

I need support. I need to calculate the average time of each process in new measure. But I need to set up a condition in the new measure. Unfortunately it did not work.

Below you will find the demo data. Process A, B and C have type full. In process D there are two different types. Avg should be for each type.
For example,

if process = D, then Calculate((sum of time for full/ count of process for full)+(sum of time for part/ count of process for part)) otherwise Calculate((sum of time/ count of process))

Note that: I used "calculate" because the average value could be changed based on the date filter.

Process	Type	Time	Date
A	full	20	03.04.2024
A	full	18	04.04.2024
A	full	16	05.04.2024
B	full	14	06.04.2024
C	full	15	07.04.2024
B	full	17	08.04.2024
C	full	19	09.04.2024
D	partial	6	10.04.2024
D	full	18	11.04.2024
D	partial	4	12.04.2024
D	full	18	13.04.2024
D	full	20	14.04.2024

[ekramulmostaqui]

Hi @danextian,
Thank you very much for your solution. I have tried your solution. Unfortunately, it showed me the wrong calculation with my real data.

Another information I share with you might help. There is exception in my data source. For this exception, the time value is empty. So we need to calculate the average without the exception.

Process	Type	Time	Date
A	full	20	03.04.2024
A	full	18	04.04.2024
A	full	16	05.04.2024
B	full	14	06.04.2024
C	full	15	07.04.2024
B	full	17	08.04.2024
C	full	19	09.04.2024
D	partial	6	10.04.2024
D	full	18	11.04.2024
D	partial	4	12.04.2024
D	full	18	13.04.2024
D	full	20	14.04.2024
C	Exception		15.04.2024
D	Exception		16.04.2024

Answer:

A	18
B	15,5
C	17
D	23,66667

[danextian]

Hi @ekramulmostaqui ,

Create these two measures first:

Sum by Type = 
CALCULATE ( SUM ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Process] ) )

Count by Type = 
CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Process] ) )

Then this measure:

Average = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Process], 'Table'[Type] ),
        "@Average", DIVIDE ( [Sum by Type], [Count by Type] )
    ),
    [@Average]
)

 Please see attached sample pbix

[danextian · Accepted solution]

You amy exclude exception from the average

Average =
CALCULATE (
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( 'Table', 'Table'[Type] <> "Exception" ),
                'Table'[Process],
                'Table'[Type]
            ),
            "@Average", DIVIDE ( [Sum by Type], [Count by Type] )
        ),
        [@Average]
    )
)

or from both the sum and count

Count by Type = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Process] ),
    FILTER ( 'Table', 'Table'[Type] <> "Exception" )
)

Sum by Type = 
CALCULATE (
    SUM ( 'Table'[Time] ),
    ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Process] ),
    FILTER ( 'table', 'Table'[Type] <> "Exception" )
)

[ekramulmostaqui]

Thank you for your solution. It worked