Average for the month of text data

Enan — Sun, 19 Feb 2023 17:48:35 GMT

Hi Expert,

I want to take average for the month ffor the attached data sets.

I need to consider only Sickess from the Main Category column.

I have one Fact table as per below and one DIM table which contain date.

Apprecaite your assistance.

Expected Result - Average Sickness for Feb-23 = 2.5

Date	Employee No.	Code	Main Category
01-02-23	123456	SICK	Sickness
01-02-23	123457	SICK	Sickness
01-02-23	123458	SICK	Sickness
01-02-23	123459	LVE	Others
01-02-23	123460	LVE	Others
01-02-23	123461	LVE	Others
02-02-23	123462	LVE	Others
02-02-23	123463	LVE	Others
02-02-23	123464	COVD	COVD
02-02-23	123465	COVD	COVD
02-02-23	123466	COVD	COVD
02-02-23	123467	COVD	COVD
02-02-23	123468	COVD	COVD
02-02-23	123469	NOAV	Others
02-02-23	123470	NOAV	Others
02-02-23	123471	NOAV	Others
02-02-23	123472	NOAV	Others
02-02-23	123473	NOAV	Others
02-02-23	123474	NOAV	Others
03-02-23	123456	SICK	Sickness
03-02-23	123457	SICK	Sickness
03-02-23	123458	SICK	Sickness
03-02-23	123459	LVE	Others
03-02-23	123460	LVE	Others
03-02-23	123461	LVE	Others
03-02-23	123462	LVE	Others
03-02-23	123463	LVE	Others
03-02-23	123464	COVD	COVD
03-02-23	123465	COVD	COVD
03-02-23	123466	COVD	COVD
04-02-23	123467	COVD	COVD
04-02-23	123468	COVD	COVD
04-02-23	123469	NOAV	Others
04-02-23	123470	NOAV	Others
04-02-23	123471	NOAV	Others
04-02-23	123472	NOAV	Others
04-02-23	123473	NOAV	Others
04-02-23	123474	NOAV	Others
04-02-23	123456	SICK	Sickness
04-02-23	123457	SICK	Sickness
04-02-23	123458	SICK	Sickness
04-02-23	123459	LVE	Others
05-02-23	123460	LVE	Others
06-02-23	123461	LVE	Others
06-02-23	123462	LVE	Others
06-02-23	123463	LVE	Others
06-02-23	123464	COVD	COVD
06-02-23	123465	COVD	COVD
07-02-23	123466	COVD	COVD
07-02-23	123467	COVD	COVD
07-02-23	123468	COVD	COVD
07-02-23	123469	NOAV	Others
07-02-23	123470	NOAV	Others
08-02-23	123471	NOAV	Others
08-02-23	123472	NOAV	Others
08-02-23	123473	NOAV	Others
08-02-23	123474	NOAV	Others
08-02-23	123456	SICK	Sickness
08-02-23	123457	SICK	Sickness
09-02-23	123458	SICK	Sickness
09-02-23	123459	LVE	Others
09-02-23	123460	LVE	Others
09-02-23	123461	LVE	Others
09-02-23	123462	LVE	Others
09-02-23	123463	LVE	Others
10-02-23	123464	COVD	COVD
10-02-23	123465	COVD	COVD
10-02-23	123466	COVD	COVD
10-02-23	123467	COVD	COVD
11-02-23	123468	COVD	COVD
11-02-23	123469	NOAV	Others
11-02-23	123470	NOAV	Others
11-02-23	123471	NOAV	Others
12-02-23	123472	NOAV	Others
12-02-23	123473	NOAV	Others
12-02-23	123474	NOAV	Others
12-02-23	123456	SICK	Sickness
12-02-23	123457	SICK	Sickness
12-02-23	123458	SICK	Sickness
12-02-23	123459	LVE	Others
12-02-23	123460	LVE	Others
13-02-23	123461	LVE	Others
13-02-23	123462	LVE	Others
13-02-23	123463	LVE	Others
13-02-23	123464	COVD	COVD
13-02-23	123465	COVD	COVD
13-02-23	123466	COVD	COVD
14-02-23	123467	COVD	COVD
14-02-23	123468	COVD	COVD
14-02-23	123469	NOAV	Others
14-02-23	123470	NOAV	Others
14-02-23	123471	NOAV	Others
14-02-23	123472	NOAV	Others
14-02-23	123473	NOAV	Others

Re: Average for the month of text data

mark_endicott — Mon, 24 Jun 2024 16:33:46 GMT

Enan - Try this in a measure:

VAR sick = CALCULATE( COUNT( Table[Employee No]), KEEPFILTERS( Table[Main Category] = "Sickness")) VAR _total = CALCULATE( COUNT( Table[Employee No]), REMOVEFILTERS( Table[Main Category] )) RETURN DIVIDE( sick, _total, 0 )

Screenshot below shows its working with the data available:

If this works, please mark it as the solution.

Re: Average for the month of text data

mark_endicott — Thu, 27 Jun 2024 09:45:25 GMT

Enan - Did this work? Please could you mark it as the solution if yes, this helps other users find it.

topic Re: Average for the month of text data in DAX Commands and Tips

Average for the month of text data

Re: Average for the month of text data

Re: Average for the month of text data