Forum Discussion
Calculated Average Measure, when blank return NA
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.
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! 🙂
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
2 Replies
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! 🙂
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601