cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OliTFD
Regular Visitor

How to calculate the average count of rows? Persons with dif. start and end dates in dif. groups

I have a table with different persons, all with different start and end dates (of employment).
I want to calculate the count of them for eyery time. But not only for every day, I also need to find the average for time periods month and quarter.
The persons belong to different groups. Here is where I get lost:
If I calculate countrows in a calculated column of the datetable, filtering on the groups is not applied to this calculation.
So any following calculation like average is not influenced by the group. Even filtering with something like
Persons[Group]=SELECTEDVALUE(Persons[Group]) does not work, because measures can not be used inside calculated colums, as I learned.
Any idea how to solve this?

The table is simple like

NameGroupStartdateEnddate
A101.01.2123.03.21
B201.02.2109.10.21
C301.04.2120.12.21
D402.05.2106.09.21
E113.02.2112.04.21
F202.06.2123.07.21
G303.04.2128.10.21
H405.07.2104.09.21
I112.06.2113.11.21
J201.01.2123.08.21
K301.02.2109.10.21
L401.04.2120.12.21
M102.05.2106.09.21


Datetable is simple Calendarauto. No relationship between the two tables.
The (not correctly working) calculated colum was

Count of active Persons per Day =
CALCULATE(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date])),
FILTER(Persons,Persons[Enddate]>=(Days[Date])),
FILTER(Persons,Persons[Group]=SELECTEDVALUE(Persons[Group]))
)
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@OliTFD , Try like

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) && Persons[Group]=SELECTEDVALUE(Persons[Group]))
))

 

or

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) )
))



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@OliTFD , Try like

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) && Persons[Group]=SELECTEDVALUE(Persons[Group]))
))

 

or

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) )
))



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Great! Works like magic! Thank you!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors