Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How to get average based on the criteria but exclude those criteria with zero value?
For example:
Row 1 = Sum(Criteria 1:Criteria 10)/ No of criteria with value (10)
Row 2 = Sum(Criteria 1:Criteria 10)/ No of criteria with value (5)
Overall = Sum of all criteria
Any formula that I can use to get the answer?
Thank you.
Solved! Go to Solution.
@sglee At first I thought you have blank values instead of zero. One quick solution is to replace all zero with blank
Or else create a measure
Measure =
VAR _sum = SUMX('Table','Table'[Criteria1]+'Table'[Criteria2]+'Table'[Criteria3]+'Table'[Criteria4])
VAR _count = SUMX('Table',CALCULATE(COUNT('Table'[Criteria1]),'Table'[Criteria1]>0)+CALCULATE(COUNT('Table'[Criteria2]),'Table'[Criteria2]>0)+CALCULATE(COUNT('Table'[Criteria3]),'Table'[Criteria3]>0)+CALCULATE(COUNT('Table'[Criteria4]),'Table'[Criteria4]>0))
RETURN DIVIDE(_sum,_count,0)
here I included only four columns. Please include other columns as well.
@sglee Please go to edit query
select all the columns that needs to be considered in average calculation
click on add column tab
Click on statistics and select average from dropdown.
If this post helps accept as solution.
@Anonymous By doing this, did not exclude the zero value criteria.
For Example Criteria 1 = 4 Criteria 2 = 4 Criteria 3 = 0
What i want is Average = (4+4)/2 = 4
Result i get is Average = (4+4)/3 = 2.67
@sglee At first I thought you have blank values instead of zero. One quick solution is to replace all zero with blank
Or else create a measure
Measure =
VAR _sum = SUMX('Table','Table'[Criteria1]+'Table'[Criteria2]+'Table'[Criteria3]+'Table'[Criteria4])
VAR _count = SUMX('Table',CALCULATE(COUNT('Table'[Criteria1]),'Table'[Criteria1]>0)+CALCULATE(COUNT('Table'[Criteria2]),'Table'[Criteria2]>0)+CALCULATE(COUNT('Table'[Criteria3]),'Table'[Criteria3]>0)+CALCULATE(COUNT('Table'[Criteria4]),'Table'[Criteria4]>0))
RETURN DIVIDE(_sum,_count,0)
here I included only four columns. Please include other columns as well.
@Anonymous Thank you! The solution work.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |