Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MADness449
Frequent Visitor

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

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:

MADness449_1-1695229478898.png

 

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.

MADness449_0-1695227271280.png

Any improvements for the logic or formula would be appreciated!

 

1 ACCEPTED SOLUTION
AmiraBedh
Resident Rockstar
Resident Rockstar

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.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

1 REPLY 1
AmiraBedh
Resident Rockstar
Resident Rockstar

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.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors