Average if non zero of measure

[Tevon713]

Hi all,

I need help in attempt transform excel model into power bi. Created the calc measure to count. The trouble lie in seeking the average if non zero of this calc measure. Got an error message stating function AVERAGEX cannot work with values of type Boolean. Maybe I'm overthinking something. Thank you.

Excel Model

CountIF=COUNTIF(B5:B39,"<>0")

AverageIF=AVERAGEIF(B5:B39,"<>0",B5:B39)

PowerBi Model

Ct _Measure = CALCULATE(COUNT(Data[Trn Units]),FILTER('Data',Data[Claim]="Y" && Data[Name]="GeneralName"))

Avg _Measure = CALCULATE(AVERAGEX(VALUES('Data'[Acct]),([Ct _Measure]<>0 || [Ct _Measure]<>BLANK())))

[danextian · Accepted solution]

Hi @Tevon713 

You are getting that error because you are trying to compare a measure against 0 and that comparison returns  a boolean value which cannot be included in the expression.

 [Ct _Measure] <> 0 || [Ct _Measure] <> BLANK ()

 Try the following

Avg _Measure =
AVERAGEX ( VALUES ( 'Data'[Acct] ), IF ( [Ct _Measure] <> 0, [Ct _Measure] ) )

 CALCULATE is not necessary as it is implicitly applied. AVERAGEX  ignores blanks.

[Tevon713]

Thank you all especially @danextian your solution make the most sense as currently set up in my data flow.

[Tevon713]

Hi @danextian.
If I have another measure similar to Ct _Measure just it Sum_Pmt and if I wanted to distinct count those acct whose Sum_Pmt <> 0 or blank. Error message "function placeholder has been used in a true/false in a table filter expression". What am I doing wrong here?

Sum_Pmt= CALCULATE(COUNT(Data[Payments]),FILTER('Data',Data[Claim]="Y" && Data[Name]="GeneralName"))

Ct _Acct =CALCULATE(DISTINCTCOUNT(Data[Acct]),
VAR _maxValue = [Sum_Pmt]
RETURN _maxValue <>0
)

[Ashish_Mathur]

Hi,

Try this measure

Measure = countrows(filter(values(Data[Acct]),[sum_pmt]<>0))

[Tevon713]

Thank you @Ashish_Mathur. It work!!!

[Ashish_Mathur]

You are welcome.

[Shahid12523]

Avg_Measure =
AVERAGEX(
VALUES(Data[Acct]),
VAR x = CALCULATE(COUNT(Data[Trn Units]), FILTER(Data, Data[Claim] = "Y" && Data[Name] = "GeneralName"))
RETURN IF(x <> 0, x)
)

[parry2k]

@Tevon713 try this:

Avg _Measure = AVERAGEX(VALUES('Data'[Acct]),[Ct _Measure])