Result 0 if no data present

[Anonymous]

Hello all, 

Building a report in which the matrix results blanks (of course) where there is not data entry for those months. 

I am using an HASONEVALUE and AVERAGEX which gives me the results I want. I just need help swaping the blank cells in the matrix to present a 0% so the average is correct. 

Usage Average = IF (

HASONEVALUE ( Equip_Utl_Days[Period] ),

[Usage Percent Max 100],

AVERAGEX (

VALUES ( Equip_Utl_Days[Period] ),

Equip_Utl_Days[Usage Percent Max 100]

)

)

[v-lili6-msft]

hi @Anonymous 

for your case, you should need to add a if conditional in the formula to judge that only replace blank with 0 which are in the current period.

please share your sample pbix file, that will be a great help.

Regards,

Lin

[Fowmy]

@Anonymous 

Since you do not have data for those periods, no calculation takes place there. To get the correct average, you need to modify the measure as:

Usage Average = 
IF (
    HASONEVALUE ( Equip_Utl_Days[Period] ),
        [Usage Percent Max 100],
    DIVIDE(
        SUMX( 
            VALUES ( Equip_Utl_Days[Period] ),
            Equip_Utl_Days[Usage Percent Max 100]
        ),
        COUNTROWS(VALUES ( Equip_Utl_Days[Period] ))
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy , thank you for your reply.

This still leaves banks where before. 

[Fowmy]

@Anonymous 

As far as I know, that blank cannot be replaced.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy 

Thank you. 

I guess then I would create a new column in the table where you have 0 if nothing else is present for each period. 

[Fowmy]

@Anonymous 

That should work for you.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@cwalraven try to use the following DAX:

VAR _avg =

HASONEVALUE ( Equip_Utl_Days[Period] ),

[Percentage of usage max. 100],

AVERAGEX (

VALUES ( Equip_Utl_Days [Period] ),

Equip_Utl_Days[Max. 100 Usage Percentage]

)

Return

IF(ISBLANK(_avg),0,_avg)

[MFelix]

Hi @Anonymous ,

Try to add the +0 to the [Usage Percent Max 100] measure and not to this final measure.

[MFelix]

Hi @Anonymous 

Add +0 to your measure and will get 0% on your cells.

[Anonymous]

@MFelix 

Yes I do get 0% but it overwrites all results as 0%.