Formula not display correct result

[Anonymous]

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)

[TomMartens · Accepted solution]

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

[Anonymous]

Hi Tom,

Thank you so much it is working pefectly fine .

You can see the result below.

[TomMartens]

Perfect!