topic Average catagory using date filter in DAX Commands and Tips

Average catagory using date filter

ekor-sembilan — Tue, 11 Apr 2023 19:51:19 GMT

Hello,

I'm new to power bi and I'd like to measure of average from the following example table :

AreaOccupancyDate	%OfficeAreaOccupancy	OfficeAreaLabel
4/11/2023 0:00	100%	Sales
4/11/2023 0:00	83%	Finance
4/11/2023 0:00	80%	Marketing
4/11/2023 0:00	83%	HR
4/11/2023 0:00	83%	IT
4/10/2023 0:00	83%	Finance
4/10/2023 0:00	100%	Sales
4/10/2023 0:00	80%	Marketing
4/10/2023 0:00	83%	HR
4/10/2023 0:00	83%	IT
4/7/2023 0:00	100%	Sales
4/7/2023 0:00	80%	Marketing
4/7/2023 0:00	67%	Finance
4/7/2023 0:00	83%	HR
4/7/2023 0:00	100%	IT
4/6/2023 0:00	80%	Marketing
4/6/2023 0:00	100%	Sales
4/6/2023 0:00	67%	Finance
4/6/2023 0:00	83%	HR
4/6/2023 0:00	100%	IT
4/5/2023 0:00	67%	Finance
4/5/2023 0:00	83%	HR
4/5/2023 0:00	80%	Marketing
4/5/2023 0:00	100%	IT
4/4/2023 0:00	83%	HR
4/4/2023 0:00	80%	Marketing
4/4/2023 0:00	67%	Finance
4/4/2023 0:00	117%	IT
4/3/2023 0:00	80%	Marketing
4/3/2023 0:00	67%	Finance
4/3/2023 0:00	83%	HR
4/3/2023 0:00	117%	IT

Reservationdate	Finance	HR	IT	Marketing	Sales
4/11/2023	83.33%	83.33%	83.33%	80.00%	100.00%
4/10/2023	83.33%	83.33%	83.33%	80.00%	100.00%
4/7/2023	66.67%	83.33%	100%	80.00%	100.00%
4/6/2023	66.67%	83.33%	100%	80.00%	100.00%
4/5/2023	66.67%	83.33%	100%	80.00%
4/4/2023	66.67%	83.33%	100%	80.00%

Target :

OfficeAreaLabel	Average Occupancy	Highest Occupancy	Lowest Occupancy
Finance
HR
IT
Marketing
Sales

Thanks for your help

Re: Average catagory using date filter

Wilson_ — Tue, 11 Apr 2023 20:29:42 GMT

Hello ekor,

Without any further information, it looks like you should look into the matrix visual and the AVERAGE, MAX and MIN functions.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Re: Average catagory using date filter

ekor-sembilan — Wed, 12 Apr 2023 05:34:25 GMT

Hello Wilson,

Thanks for your reply.

Any references you like to share ?

Thanks

Re: Average catagory using date filter

Wilson_ — Wed, 12 Apr 2023 11:36:54 GMT

Try your friendly neighbourhood search engine, Ekor. Being able to find answers to basic questions is an incredibly important and useful skill in and of itself. 🙂

Re: Average catagory using date filter

ekor-sembilan — Thu, 13 Apr 2023 09:20:36 GMT

Hello Everyone,

I try to calculate the average from the following table.

Reservationdate	Finance	HR	IT	Marketing	Sales
4/11/2023	83.33%	83.33%	83.33%	80.00%	100.00%
4/10/2023	83.33%	83.33%	83.33%	80.00%	100.00%
4/7/2023	66.67%	83.33%	100%	80.00%	100.00%
4/6/2023	66.67%	83.33%	100%	80.00%	100.00%
4/5/2023	66.67%	83.33%	100%	80.00%
4/4/2023	66.67%	83.33%	100%	80.00%

The target result :

OfficeAreaLabel	Average Occupancy
Finance	72.22%
HR	83.33%
IT	94.44%
Marketing	80.00%
Sales	100%

and

Can any one help me , thanks

Re: Average catagory using date filter

v-zhangti — Tue, 18 Apr 2023 02:41:57 GMT

Hi, ekor-sembilan

You can try the following methods.

In the Power Query-Transform-Unpivot Other Columns:

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.