Measure to Use Average if Value is Blank

[Anonymous]

Hi,

I am trying to assign points per project and use an estimate for projects with 0 points currently assigned to them.

My data looks like this and I am basically hoping to maybe create a temp table that averages the Points by Priority and then uses that average if the inital point value is blank.      

Is that the best way to do this? I keep getting values that don't seem to make sense and obviously keeping this dynamic would be great as this is a large data set.  Any ideas?

The points adjusted I did quickly in Excel to show what the average if would be for the ones with no points.

Priority	Project	Points	Points Adjusted	Status
1	A	5	5.00	Completed
2	B	5	5.00	Completed
3	C	10	10.00	Completed
1	D	10	10.00	Completed
2	E	5	5.00	Completed
3	F	10	10.00	Completed
1	G	0	5.00	Open
2	H	0	3.33	Open

[Anonymous · Accepted solution]

Hello, lbendlin ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Anonymous .I am glad to help you.

I hope my test below will give you good advice:
Below is my test data:

The measure I created:

M_PointsAdjusted =
VAR _priority =
    MAX ( 'Table'[Priority] )
VAR _avgifblank =
    CALCULATE (
        AVERAGE ( 'Table'[Points] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Priority] = _priority )
    )
VAR _points =
    MAX ( 'Table'[Points] )
RETURN
    IF ( _points = BLANK () || _points = 0,
 _avgifblank, _points )

_priority: Get the priority of the current row.
_avgifblank: Calculates the average score of the current priority, filtering out all other priority rows.
_points: Get the score of the current row.
RETURN: Returns the average score if the current row's score is null or zero; otherwise returns the current row's score.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,@Anonymous .Thank you for your reply.
Try to use sumx to the measure.

M_PointsAdjustedChanged = 
VAR _total=SUMX('Table',[M_PointsAdjusted])
RETURN _total

[Anonymous]

This is really awesome and works great.  Thanks so much 

[Anonymous]

Hi,@Anonymous .Thank you for your reply.
You are welcome.

[Anonymous]

Hi, one quick twist.....

If you wanted the total to be the sum of the points and not the average, is that possible? I am guessing it is a sumx of the average but can't seem to get it to work.

Thanks

[Anonymous]

The motivation behind it is that I am analyzing 1000's of projects and some of them close same day or have missing dates and there are others that actually have data that I need to present(lasting weeks, months or even years).  Obviously these skew metrics so I was hoping to present some metrics both with and without these zeroes.  

Hopefully that hleps but I would also use it for customers who pay on time versus people that take longer. Most customers pay within the window so it would be good to know both ways. 

Thanks

[lbendlin]

You would have to volunteer a bit more information regarding your business logic and motivation behind it.  "Averages and blank values" is a rather touchy subject.