Forum Discussion
Harmonic Mean
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.
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.
5 Replies
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.
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
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.
