Using accumulated average as a filter for distinctcount

[Scrambalot]

Hi,

Im having a problem. I have a table with users and how many % they have been missing from work and I'm trying to filter if they are below a certain threshold for the period chosen for example 0,15. So if i choose only 202311 both 112 and 113 should be above the threshold but if i pick the period 202310+202311 both shoud be below

UserID	Abscense	Period
112	0,16	202311
113	0,15	202311
112	0,12	202310
113	0,13	202310
112	0,1	202309

It's easy to do for the whole data set or for one spescific period but I'm having problem making it work on unique period like 202311+202310 or 202310+202309 cause the users wants a slicer. If I just use
AVERAGE it will filter the rows above the thresehold and not look att the accumulated average for the chosen period.

I've used CALC to get the following measure, CALCULATE(AVERAGE('table'[Abscense]),ALLSELECTED('table'[Period])) but I can't get it to work as a filter for a DISTICNTCOUNT(table[UserID]) becuase it's a measure.

I'm problably missing the obvious solutions, thanks in advance

[123abc]

It looks like you are trying to calculate an accumulated average for each user over a selected period and then filter users based on whether their accumulated average is below a certain threshold. You can achieve this by creating a new measure for accumulated average and then using that measure in combination with a DISTINCTCOUNT measure for filtering.

Here's a step-by-step guide:

1. Create a measure for accumulated average:

AccumulatedAverage =
CALCULATE(
AVERAGE('table'[Abscense]),
FILTER(
ALLSELECTED('table'[Period]),
'table'[Period] <= MAX('table'[Period])
)
)

1. This measure calculates the accumulated average for each user up to the selected period.

2. Create a measure for distinct count of users below the threshold:\

UsersBelowThreshold =
CALCULATE(
DISTINCTCOUNT('table'[UserID]),
'table'[AccumulatedAverage] < 0.15
)

1. This measure calculates the distinct count of users whose accumulated average is below the specified threshold (0.15 in your example).

Now, when you use the UsersBelowThreshold measure in your report or visualization, it will dynamically count the number of users whose accumulated average is below the threshold for the selected period using the slicer.

Make sure to adjust the threshold value in the UsersBelowThreshold measure if you want to use a different threshold.

[Scrambalot]

Hi!

Thanks for the quick response. I won't be able to use a calulated column for this due to the this scenario:

Lets say I have a user that we have data for the last 12 month but the manager want to specifically look att the months 3-7 or 2-9 which means then average for that period will be unique and can't be reached using a colum.

So the AVERAGE I want to filter by has to be dynamic based on which months that are picked.

[123abc · Accepted solution]

Create a Date Table: Ensure that you have a date table in your Power BI model. If you don't have one, you can create one by going to "Modeling" and selecting "New Table." Use a formula like the following to create a date table:

DateTable = CALENDAR(MIN('YourData'[Date]), MAX('YourData'[Date]))

1. Create a Slicer: Create a slicer in your report that allows users to select the months they are interested in.

2. Create a Measure for Selected Period: Create a measure that dynamically calculates the average based on the selected months. Assuming your data has a numeric column called 'Value' that you want to average, and your date table is 'DateTable', the measure could look like this:

AverageSelectedPeriod =
CALCULATE(
AVERAGE('YourData'[Value]),
FILTER(
ALL('DateTable'),
'DateTable'[Date] >= MIN('DateTable'[Date]) && 'DateTable'[Date] <= MAX('DateTable'[Date])
)
)

1. This measure uses the CALCULATE function to change the context of the data being evaluated. The FILTER function is used to dynamically filter the data based on the selected date range.

2. Display the Measure: Now, you can use this measure in your report. When users select a different period using the slicer, the average will be dynamically calculated based on their selection.

Remember to replace 'YourData' and 'Value' with your actual data table and column names.

This approach allows you to dynamically calculate the average based on the selected months using a measure, giving you the flexibility to adjust the date range as needed.