Calculating HHI (Hirschmann Herfindahl Index)

[Anonymous]

I would like to be able to calculate the HHI (Hirschmann Herfindal Index)

The goal is to have a table where each row is for one industry.  I then want the HHI for that industry which would be calculated by calculating the market share of each company and then squaring each market share.  I would then need to add up all the results of the market shares squared for each company to get the HHI for the industry.  

I would like guidance on how to calculate these formulas as the company names will not be showing in the industry table.

Thank You!

[richbenmintz]

Hi @Anonymous,

Any chance you could provide some sample data and the expected outcome?

Thanks,

Richard

[Anonymous]

@richbenmintz - I can't upload my data due to compliance reasons.  Goal outcome is:

Industry	HHI
Manufacturing	.8
Technology	.2
Energy	.5

There are many companies that have to have their market share squared within each of the industries 

[richbenmintz]

Hi @Anonymous,

so to "kiss" this request I would create a calculated column to calc the the squared market share for each company like 

Squared Market Share = POWER([market_share]), 2)

Then you just need to create a measure HHI

HHI = sum('table'[Squared Market Share])

Hope that helps,

Richard 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

@richbenmintz  - really appreciate your help here!

Here is what I have so far:

Market Share:  numerator = sum('table[revenue])

                   denominator = calculate([numerator], ALLSELECTED (table))

                   market share calculation = DIVIDE([numerator],[denominator])

HHI:  Step 1: Power([market share calculation],2)

         Step 2: This is the formula that is not working properly for me.  I am attempting =sum(table[HHI Step 1])

                  - HHI step 1 does not appear as an option after inputting the table name for my data input.

Please advise if all my formulas are correct and how to solve HHI step 2 issue - thank you!

[richbenmintz]

@Anonymous,

I created some mock data

industry company revenue

industry 1	company 1	1000
industry 1	company 2	2000
industry 1	company 3	3000
industry 1	company 4	4000
industry 2	company 5	2000
industry 2	company 6	3000
industry 2	company 7	7000
industry 2	company 8	10000

and came up with the following

Market Share HHI = 
    var rev = sum(HHI[revenue])
    var totalIndustryRev = CALCULATE(sum(HHI[revenue]), ALL('HHI'[company]))
return if(HASONEVALUE('HHI'[industry]), power(divide(rev, totalIndustryRev), 2))

HHI Val = if(HASONEVALUE(HHI[industry]), SUMX(VALUES(HHI[company]), [Market Share HHI]) )

I hope this helps,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

@richbenmintz - thank you very much for taking the time to write that out for me!  

Your formula includes:

(HASONEVALUE('HHI'[industry])

Is there a way to make this formula more dynamic to not include [industry] in both formulas?  For example: I may occassionally change the first column in the table from industry and make it product.  Would be nice to not have to edit the formula each time/have two HHI formulas and swap the other in. 

Thank You 

[richbenmintz]

Hi @Anonymous ,

I think you could generalize it a little more with the following

Market Share HHI = 
    var rev = sum(HHI[revenue])
    var totalIndustryRev = CALCULATE(sum(HHI[revenue]), ALL('HHI'[company]))
    var totalProductRev = CALCULATE(sum(HHI[revenue]), ALL('HHI'))
return 
    SWITCH(TRUE(), 
        HASONEVALUE('HHI'[Product]), power(divide(rev, totalProductRev),2),
        HASONEVALUE('HHI'[industry]), power(divide(rev, totalIndustryRev),2)
    )

HHI Val = 
      SWITCH(TRUE(), 
       HASONEVALUE(HHI[Product]), SUMX(HHI, [Market Share HHI]),
        HASONEVALUE(HHI[industry]), SUMX(VALUES(HHI[company]), [Market Share HHI]) 
    )

Hope this helps,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!