Instead of blank value need to fill 0

[Anonymous]

Hi,

I have one matrix where we have not the data for selected category and country, So I want to fill this blank with 0 value.

RFT CUC =
var gap = CALCULATE(distinctCOUNT(TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[SPECIFICATION]),FILTER(TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC,TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[RFT_REMARK]="No_Gap"))
var c = CALCULATE(distinctCOUNT(TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[SPECIFICATION]),REMOVEFILTERS(TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[RFT_REMARK]))
var per= DIVIDE(gap,c,BLANK())
var res=COALESCE(per,0)
return res

I am using this DAX to calculate this percentage.

Please help me to do this

[v-janeyg-msft]

Hi, @Anonymous 

The information you provided is vague and it is hard for us to judge.

You can try:

RETURN
    IF ( res = BLANK (), 0, res )

If total is displayed incorrectly, you also need to use sum(summarize ()) to calculate total.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[Pragati11 · Accepted solution]

Hi @Anonymous ,

I think you can just modify your DAX as follows:

RFT CUC =
VAR gap =
    CALCULATE (
        DISTINCTCOUNT ( TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[SPECIFICATION] ),
        FILTER (
            TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC,
            TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[RFT_REMARK] = "No_Gap"
        )
    )
VAR c =
    CALCULATE (
        DISTINCTCOUNT ( TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[SPECIFICATION] ),
        REMOVEFILTERS ( TT_POWER_BI_DQ_SCORE_FTR_COMBINED_CUC[RFT_REMARK] )
    )
VAR per =
    DIVIDE ( gap, c, BLANK () )
VAR res =
    COALESCE ( per, 0 )
RETURN
    res + 0

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

This solution is not working. when I have applied that this dax the result is same.
Actually the problem is I don't have the combination of that category and country in my data table.