Calculated Average Measure, when blank return NA

[aaron797]

Hi,

I have the below formula, when adding into a table, how do i return all the Blanks (Empty spaces) as a string "N/A?

Measure = 
CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))

Any idea how this can be achieved, can you wrap Calculates in an IF RETURN statement perhaps? I am new to the DAX syntax so any tips would be appreciated. 

[JarroVGIT · Accepted solution]

Hi @aaron797 ,

You can use an IF statement indeed, and for readability I would recommend something like this:

Measure = 
VAR _calculation = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
RETURN
IF(_calculation = "", "N/A", _calculation)

You might have to tweak it a little, like replacing the <_calculation = ""> bit with "ISBLANK(_calculation)". Haven't tested the above, hopes it helps 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[JarroVGIT · Accepted solution]

Hi @aaron797 ,

You can use an IF statement indeed, and for readability I would recommend something like this:

Measure = 
VAR _calculation = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
RETURN
IF(_calculation = "", "N/A", _calculation)

You might have to tweak it a little, like replacing the <_calculation = ""> bit with "ISBLANK(_calculation)". Haven't tested the above, hopes it helps 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[amitchandak · Accepted solution]

Try

Measure = 
var _t1 = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
return if(isblank(_t1),"N/A",_t1)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601