Harmonic Mean

[joansinho]

Hi everyone,

I am trying to use the harmonic mean, but I noticed powerBI does not have that function. I am trying to write it from scratch, but I am running into some troubles. Also, I need to group by account names. I have 80 accounts names with 50,000 users. Each user belongs to an account.

Harmean = n/( sum for all xi(1/xi))

What I have so far:

PowerBI_Harmean_Group_by_Account = filter('table1', 'table1'[Account_Name], CountRows('table'[column3])/(sum(1/('table1'[column3])))

Thank you in advance. 

[Chihiro]

Hmm, is CountRows('table'[column3]), a typo of CountRows('table1'[column3])?

Or is it different table from 'table1'?

And are you trying to calculate harmonic mean of Account Name & count of user?

I.e. Harmean = 80/(sum for all xi(1/xi))

It would help if you can upload small sample file representative of your table. Along with expected result from calculation.

[joansinho]

@Chihiro

Thank you for your prompt response.

That was a typo.

This is a small sample of what I haveThe Harmean column is what I want to accomplish. I can do it in excel , But I need to do in PowerBIThis would be my main goal.

I am calculating the harmean at Column3 ("Uses"), by grouping the users and the Account Name.

Regards,

Joansinho

[Chihiro · Accepted solution]

I think you should be able to do something like...

n = CountRows('table')

d = SUMX('table', 1/[Uses])

Harmean = [n]/[d]

And put it into Matrix visual.

Note: I think your calculation for A-Business is off, and you are doing avg of avg for A & B sub total.

[joansinho]

@Chihiro

Thank you!

Yes, I just noticed I grabbed the wrong value A Business.

I think your explanation is great about calculating the Harmean. I will create two measures "n" and "d" and then a "column = n/d" to obtain the Harmean. Last one question. How do I make sure they group by A and B and by Account name. In excel I used something like this:   "{HARMEAN(IF($C$2:$C$3229=C2,$A$2:$A$3229))}"  to accomplish my goal.

And, Yes! I created I pivot table and I got the average of the Harmean.

Best,

[Chihiro]

You are welcome 🙂

As for your question.

"Last one question. How do I make sure they group by A and B and by Account name."

DAX, is always calculated in context. Meaning, Filtering and Row context within visual is vital in how it's evaluated.

So structuring your visual is key in displaying correct values.

Other than that, you could use Filter() function to manipulate context, but I'd not recommend it, as you'll need to hard code conditions in the measuer.