Forum Discussion
Formula not display correct result
Hi,
Please see below formula,It gives me the correct result but result display is not correct.
When below forumla condition does not meet it should display 0 in a current d rows but it is showing totally empty rows.CURRENT D column data type is decimal with currency format.
CURRENT D = IFERROR(CALCULATE(AVERAGE('MS'[Valid Price]),
FILTER(ALL('MS '[REGION]),'MS '[REGION]="AMERICAS"),
FILTER('MS ','MS '[Unique ]=RELATED('MS Supporting Column'[Pricing Id])),
FILTER('MS ','MS '[Contract Star]="Y"),
FILTER('MS ','MS '[Contract End]="N")),0)
Hey,
I'm not sure, what kind of error you try to catch, AVERAGE(..., ...) returns BLANK() if the application of the filter from the CALCULATE filter out all rows in the current FILTER context.Maybe you should give ISBLANK() a try like so:
var thevalue = CALCULATE(<your CALCULATE(...)>) return IF(ISBLANK(thevalue), 0, thevalue)
If this does not help, maybe you can provide a pbix file with some sample data, upload the pbix to onedrive or dropbox and share the link.
Regards,
Tom
3 Replies
Hey,
I'm not sure, what kind of error you try to catch, AVERAGE(..., ...) returns BLANK() if the application of the filter from the CALCULATE filter out all rows in the current FILTER context.Maybe you should give ISBLANK() a try like so:
var thevalue = CALCULATE(<your CALCULATE(...)>) return IF(ISBLANK(thevalue), 0, thevalue)
If this does not help, maybe you can provide a pbix file with some sample data, upload the pbix to onedrive or dropbox and share the link.
Regards,
Tom
- Perfect!