Create a measure to return a certain value when it matches a condition

[Anonymous]

Hi all,

I wanted to create a measure to return the "Wait Time" value when the Store ID = National.

The table called ("Performance_Latest") is like:

Store ID       Wait Time

111              80%

222              90%

National      88%

The formula i used is:

WaitTime_National = CALCULATE(AVERAGE(Performance_Latest[Wait Time]), Performance_Latest[Store ID]="National")

but it didn't work and it returned blank.

Do you know how to fix it?

Thank you so much!

[sebastiangestia]

Hello @Wenlu_Zhang,

I get the part - "National"... What do you mean?

That may be affecting, as it is a text applied to the ID for the CALCULATE.

Best regards!

[AllisonKennedy]

Do you have any other filters applied that could cause it to return blank?

What is the data type for Wait Time column?

Is National spelled EXACTLY the same in the table as it is in your measure?

[harshnathani]

Hi @Anonymous ,

You measure should work

Try this

WaitTime_National = CALCULATE(SUM(Performance_Latest[Wait Time]), Performance_Latest[Store ID]="National")

else are there any filters

Regards,

Harsh Nathani

[amitchandak · Accepted solution]

@Anonymous , I doubt some other filter is there.

Check if this gives data. It means some other filter is there

WaitTime_National = CALCULATE(AVERAGE(Performance_Latest[Wait Time]),filter(all(Performance_Latest), Performance_Latest[Store ID]="National"))