Need your help to calculate average value based on the distinct count & date

[Anonymous]

Hi,

I'm looking for your help to create the measure for the below table

Date	Badge	Total
31/08/2021	A	95.5
31/08/2021	A	98.2
30/08/2021	B	91.2
31/08/2021	B	93.3
31/08/2021	B	96.4
30/08/2021	C	95.5
30/08/2021	D	98.2
30/08/2021	E	91.2
28/08/2021	F	93.3
29/08/2021	F	96.4

I want to calculate the count of distinct badges based on the below conditions average value. 

      a) Distinct count of badge based on Average value above 90

      b) Distinct count of badge based on Average value between 90 to 75

      c) Distinct count of badge based on Average value below 75

I'm going to use the "Date" as a slicer. if I'm I select a particular date from the slicer, it shows the distinct count of badges based on the average value condition.

For example, the output for 31/08/2021 is 

           1) > 90 =  2

           2)  90 -75 = 1

            3) < 75 =  1

[Jos_Woolley]

Glad to know! Cheers.

[Jos_Woolley · Accepted solution]

Hi Sakthi,

Thanks for clarifying.

Measure1 =
VAR MyTable =
    SUMMARIZE (
        'Table',
        'Table'[Badge],
        "Average Score", AVERAGE ( 'Table'[Total] )
    )
RETURN
    SUMX ( MyTable, 0 + ( [Average Score] > 90 ) )

Measure2 =
VAR MyTable =
    SUMMARIZE (
        'Table',
        'Table'[Badge],
        "Average Score", AVERAGE ( 'Table'[Total] )
    )
RETURN
    SUMX ( MyTable, ( [Average Score] >= 75 ) * ( [Average Score] <= 90 ) )

Measure3 =
VAR MyTable =
    SUMMARIZE (
        'Table',
        'Table'[Badge],
        "Average Score", AVERAGE ( 'Table'[Total] )
    )
RETURN
    SUMX ( MyTable, 0 + ( [Average Score] < 75 ) )

Regards

[Anonymous]

Thank you Jos.. Really it is working perfectly. 

[Jos_Woolley]

Hi,

Can you clarify? Your posted data only has values greater than 90, so I'm confused as to how you could ever arrive at an average less than 90 from that dataset.

Regards

[Anonymous]

Hi Jos, 

Sorry for the confusion. please find below the corrected dataset.

Date	Badge	Total
31/08/2021	A	95.5
31/08/2021	A	98.2
30/08/2021	B	91.2
31/08/2021	B	93.3
31/08/2021	B	96.4
31/08/2021	C	80.2
31/08/2021	C	85.2
30/08/2021	D	98.2
30/08/2021	E	91.2
28/08/2021	F	93.3
31/08/2021	F	74.2