topic Re: Need your help to calculate average value based on the distinct count & date in DAX Commands and Tips

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

Anonymous — Tue, 31 Aug 2021 14:35:42 GMT

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

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

Jos_Woolley — Tue, 31 Aug 2021 15:55:37 GMT

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

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

Anonymous — Wed, 01 Sep 2021 00:27:27 GMT

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

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

Jos_Woolley — Wed, 01 Sep 2021 05:19:07 GMT

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

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

Anonymous — Wed, 01 Sep 2021 06:37:17 GMT

Thank you Jos.. Really it is working perfectly.

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

Jos_Woolley — Wed, 01 Sep 2021 06:41:17 GMT

Glad to know! Cheers.