Matrix Percentages - Change Blanks to Zero

[dm1904]

I've tried for hours to solve this one 😕 Any help would be greatly appreciate. Basically I have a matrix that tracks progress percentages over time. I want the maximum percentage to be shown in each cell, for the period dictated by my "Progress Date" Slider. The functionality of the slider and agrgregation of percentages seems to be working ok apart from where blanks are involved.

Where no progress date for an item has been reported, the matrix treats the resultant percentage as 0% which is perfect. However, where a progress date has been reported and it falls outside the range of my date filter, the result turns to a blank. This wouldn't be an issue on an individual cell basis, but the blanks aren't treated as zeros for the auto aggregation of percentages by the matrix visual. Therefore giving me false results on column and row totals. 

I've tried adding "+0" to my measure. And I've also tried IF(IsBlank(Measure),0,(measure)). No success with either. In a nutshell please help me treat all blanks as zeros!

Measure - Max of Percent Complete average per Progress Lookup =

AVERAGEX(

    KEEPFILTERS(VALUES('Master Fit Out Tracker'[Progress Lookup])),

    CALCULATE(MAX('Master Fit Out Tracker'[Percent Complete])))

Column - Percent Complete = IF(ISBLANK('Master Fit Out Tracker'[Fit Out Tracker.Progress Percent]),0,'Master Fit Out Tracker'[Fit Out Tracker.Progress Percent]/100)

Column - Progress Date = IF('Master Fit Out Tracker'[Fit Out Tracker.Progress Percent]=100,'Master Fit Out Tracker'[Fit Out Tracker.Completion Date],'Master Fit Out Tracker'[Fit Out Tracker.Created])

[Greg_Deckler]

Hard to tell, probably need to understand how your matrix is setup. See Table 16 and Page 12 of attached.

[dm1904]

The biggest diference in the matrix is the fact that I'm using hierarchies for both columns and rows. Everything works fine if in date range, but it has something to do with the date column and slicer surely 😕

[v-juanli-msft]

Hi @dm1904

To make it works with the date column and slicer surely, you need to create a measure instead of a column 

Measures below

Percent Complete = IF(ISBLANK(MAX([Fit Out Tracker.Progress Percent])),0,MAX([Fit Out Tracker.Progress Percent]))

Measure = AVERAGEX(KEEPFILTERS(VALUES(Sheet4[sub_category])),[Percent Complete])

Best Regards

Maggie

[Greg_Deckler]

Try making your currently formula a VAR and then RETURN IF(ISBLANK(__variable),0,__variable), where __variable is the variable you create with VAR.

[dm1904]

@Greg_Deckler Thank you for your response. I'm venturing outside my comfort zone here which is never a bad thing. I've tried the below without success. Any obvious mistakes?

Max of Percent Complete average per Progress Lookup =

var percentage =

AVERAGEX(

    KEEPFILTERS(VALUES('Master Fit Out Tracker'[Progress Lookup])),

    CALCULATE(MAX('Master Fit Out Tracker'[Percent Complete])))

RETURN IF(ISBLANK(percentage),0,percentage)

[Greg_Deckler]

That looks correct, what error are you getting?

[dm1904]

@Greg_Deckler No error, but the Var had no effect on the outcome. Still getting the blank values