Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dm1904
Helper I
Helper I

Matrix Percentages - Change Blanks to Zero

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])
 
Sketch (4).png
7 REPLIES 7
Greg_Deckler
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 😕

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])

8.png

 

Best Regards

Maggie

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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)

That looks correct, what error are you getting?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.