topic Weeks with no presence and avg presence banding in DAX Commands and Tips

Weeks with no presence and avg presence banding

gmunteaney — Fri, 11 Jul 2025 06:36:35 GMT

Hi,

I need some help on how can I count/sum how many weeks people have no present in the office.

I have daily IP data for individual, DateKey table and HR data with individuals details.

For now I have.

% Average Presence = iferror( IP[Total Presence]/('HR'[Total population]*DateKey[Working Days]),blank())

Avg Presence Days Per Week per Person = iferror([% Average Presence]*[Days in a week],blank())

0 Presence = if([Avg Presence Days Per Week per Person]=0,1,0)

Avg. Presence Per Week =

var _Presence = IP[Total Presence]/DateKey[Weeks no.]

Return

If (ISBLANK(_Presence),0,_Presence)

I also need to create a filter where I can show the list with people who fall under the falling categories also if I have to create a visual to show number of the invdividuals falling under ech category :

[Avg Presence Days Per Week per Person] <= 1, "1 Day or Less",

[Avg Presence Days Per Week per Person] <= 2, "1-2 Days",

[Avg Presence Days Per Week per Person] > 2, "More Than 2 Days"

Thanks.

Re: Weeks with no presence and avg presence banding

FBergamaschi — Fri, 11 Jul 2025 15:12:33 GMT

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

Re: Weeks with no presence and avg presence banding

v-ssriganesh — Mon, 14 Jul 2025 04:35:22 GMT

Hello gmunteaney,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario using a sample dataset in Power BI Desktop and I was able to achieve the expected output based on your requirements.

I implemented:

Calculation of average presence days per person per week
Count of weeks with zero presence per individual
Banding logic to group people into categories:
"1 Day or Less", "1-2 Days", "More Than 2 Days"
A matrix visual showing each individual’s stats

For your reference, I’m attaching the .pbix file so you can explore the data model, DAX measures, and visuals directly. You can adjust it further based on your actual dataset structure.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.