Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
How do I put this in DAX without having too many IFs:
I have measures like as follows:
Measure Name Values
Measure1 1
Measure2 2
Measure3 1
Measure4
Measure5 0
I need to be able to get the Average which should be like below:
([Measure1] + [Measure2]+ [Measure3] +[Measure5])/4
Denominator is 4 because we have blank for Measure4 hence excluding it.
And any of the measure may be blank.
Another scenario:
Measure Name Values
Measure1
Measure2 2
Measure3 1
Measure4
Measure5 0
I need to be able to get the Average which should be like below:
([Measure2] + [Measure3]+ [Measure5])/4
Problem is I cannot use AVERAGE function for this.
Please help and much appreciated.
🙂
Solved! Go to Solution.
Hi, using a measure with IF :
CountMeasuresNoBlank = IF ( [Measure1] <> BLANK (); 1 ) + IF ( [Measure2] <> BLANK (), 1 ) + IF ( [Measure3] <> BLANK (), 1 ) + IF ( [Measure4] <> BLANK (), 1 )
MeasureSum = [Measure1]+[Measure2]+[Measure3]+[Measure4]
AvgMeasures = DIVIDE([MeasureSum],[CountMeasuresNoBlank])
Regards
Victor
Lima - Peru
Hi, using a measure with IF :
CountMeasuresNoBlank = IF ( [Measure1] <> BLANK (); 1 ) + IF ( [Measure2] <> BLANK (), 1 ) + IF ( [Measure3] <> BLANK (), 1 ) + IF ( [Measure4] <> BLANK (), 1 )
MeasureSum = [Measure1]+[Measure2]+[Measure3]+[Measure4]
AvgMeasures = DIVIDE([MeasureSum],[CountMeasuresNoBlank])
Regards
Victor
Lima - Peru
Hi @Vvelarde,
Thank you heap s. With your help I was able to get the correct measure to get the average. I basically just edit my measure names and put them in variables. The CountMeasuresNoBlank is a winner! Thanks!
ScoreOverall = VAR SumScores = ([Score1]+[Score2]+[Score3]+[Score4]+[Score5) VAR CountMeasuresNoBlank = IF ( [Score1] <> BLANK (), 1 ) + IF ( [Score2] <> BLANK (), 1 ) + IF ( [Score3] <> BLANK (), 1 ) + IF ( [Score4] <> BLANK (), 1 + IF ( [Score5] <> BLANK (), 1 )) + 1 RETURN ROUND(DIVIDE(SumScores,CountMeasuresNoBlank),1)
In your scenario, the values are the result from measures. It's not possible to count number of measures (as your denominator) based on their results.
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |