Forum Discussion
Using accumulated average as a filter for distinctcount
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]))
Create a Slicer: Create a slicer in your report that allows users to select the months they are interested in.
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])
)
)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.
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.
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:
Create a measure for accumulated average:
AccumulatedAverage =
CALCULATE(
AVERAGE('table'[Abscense]),
FILTER(
ALLSELECTED('table'[Period]),
'table'[Period] <= MAX('table'[Period])
)
)
This measure calculates the accumulated average for each user up to the selected period.
Create a measure for distinct count of users below the threshold:\
UsersBelowThreshold =
CALCULATE(
DISTINCTCOUNT('table'[UserID]),
'table'[AccumulatedAverage] < 0.15
)
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.
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.
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]))
Create a Slicer: Create a slicer in your report that allows users to select the months they are interested in.
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])
)
)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.
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.