Forum Discussion

joansinho's avatar
joansinho
Advocate I
7 years ago
Solved

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 gr...
Need Help
Tutorial Requests
  • Chihiro's avatar
    Chihiro
    7 years ago

    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's avatar
joansinho
Advocate I
7 years ago

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's avatar
Chihiro
Solution Sage
7 years ago

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.