Creating columns for count, min, avg, and max calculations based on parameter from another column

[MADness449]

I have a table with count, min, avg, and max for each item, however I also want to show count, min, avg, and max for items with age >=48 hours in the same table. For reference Age column has values:

Power BI does not allow me to set a filter on a specific column (applies to entire visual) so it affects the previous count, min, avg, and max calculations that I want to keep separate. I tried creating a calculated column for the minimum when age >=48 hours:

Column = IF('NCR Open Calls'[Age Sort]="less than 48",BLANK(),CALCULATE(MIN('NCR Open Calls'[AGE]),FILTER('NCR Open Calls','NCR Open Calls'[ITEM DESCRIPTION]=EARLIER('NCR Open Calls'[ITEM DESCRIPTION]))))

However, I am not getting data I would expect.

Any improvements for the logic or formula would be appreciated!

[AmiraBedh · Accepted solution]

You can start creating measures for the new count, min, avg, and max calculations that consider only items with age >= 48 hours:

CountAgeGreater48 =
CALCULATE(
COUNT('NCR Open Calls'[AGE]),
FILTER(
'NCR Open Calls',
'NCR Open Calls'[Age Sort] <> "less than 48"
)
)

Then Min for Age >= 48 hours:

MinAgeGreater48 =
CALCULATE(
MIN('NCR Open Calls'[AGE]),
FILTER(
'NCR Open Calls',
'NCR Open Calls'[Age Sort] <> "less than 48"
)
)

Then Average for Age >= 48 hours:

AvgAgeGreater48 =
CALCULATE(
AVERAGE('NCR Open Calls'[AGE]),
FILTER(
'NCR Open Calls',
'NCR Open Calls'[Age Sort] <> "less than 48"
)
)

And finally Max for Age >= 48 hours:

MaxAgeGreater48 =
CALCULATE(
MAX('NCR Open Calls'[AGE]),
FILTER(
'NCR Open Calls',
'NCR Open Calls'[Age Sort] <> "less than 48"
)
)

Now, when you use these measures in a table visual, they will automatically take the context of the visual into account. This means that, for each item description, Power BI will only consider rows with age >= 48 hours when computing the values of these measures.

This approach ensures that other calculations (your original count, min, avg, max) remain unaffected by the >= 48 Note that if `'NCR Open Calls'[Age Sort]` is a derived column from `'NCR Open Calls'[AGE]`, you can also directly filter based on the AGE column value itself. For instance, if AGE is in hours, you can replace `'NCR Open Calls'[Age Sort] <> "less than 48"` with `'NCR Open Calls'[AGE] >= 48` in the measures above.