Forum Discussion
Advocate IHarmonic Mean
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.
Advocate IThank 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
Solution SageI 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
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.