topic Re: Calculate sum or squares of column percentage values in DAX Commands and Tips

Calculate sum or squares of column percentage values

Anonymous — Sun, 07 Feb 2021 00:39:09 GMT

I'm working with market data, and I'm trying to use DAX to calculate the Herfindahl-Hirschman Index (HHI) for different markets. This is a measure of market concentration, and it calculated as the sum of the squares of market share for each vendor. Here's an example market:

Vendor	Revenue	Market Share
A	10	12.5%
B	20	25.0%
C	50	62.5%

So the HHI would be (12.5*12.5 + 25*25 + 62.5*62.5) = 4867.5. (For HHI, each percentage value is multiplied by 100.)

I have vendor revenue data by year, market, and submarket. I'd like to be able to calculate HHI at both the market and submarket level.

I can create a matrix visual by summarizing revenue per vendor as a percent of the column total, where each column is the market I'm trying to calculate the HHI for. In Excel, I calculate HHI manually using the SUMPRODUCT of the column of market share by market with itself to get the sum of squares. I'm having trouble coming up with a DAX measure or a new table to calculate HHI, though. I've tried SUMMARIZE and GROUPBY, but I'm stuck.

Any help would be greatly appreciated.

Here is a link to a .pbix file with sample data: https://1drv.ms/u/s!AgIj2L_vt8Wrg_B2jTCa8MduAaXS8A?e=HVX84o

Re: Calculate sum or squares of column percentage values

OwenAuger — Sun, 07 Feb 2021 02:31:29 GMT

Hi Anonymous

Here is how I would create a measure for HHI using the data in your PBIX (thanks for sharing by the way).

My modified PBIX is attached with an extra tab showing HHI.

First create a Revenue measure to make the code more readable:

Revenue = SUM ( 'Market Data'[Revenue - USD] )

Then create this HHI measure:

HHI = VAR TotalRevenueSquared = [Revenue] ^ 2 VAR RevenueSquaredSumPerVendor = SUMX ( VALUES ( 'Market Data'[Vendor] ), [Revenue] ^ 2 ) RETURN DIVIDE ( RevenueSquaredSumPerVendor, TotalRevenueSquared ) * 10 ^ 4

The HHI measure assumes that the HHI is calculated over all Vendors visible in the current filter context, so you shouldn't include Vendor as a field on the visual where the HHI measure is displayed.

Summary of steps:

Calculates the squared Revenue total (TotalRevenueSquared)
Sums the squared revenue per Vendor (RevenueSquaredSumPerVendor)
Divides 1 by 2, then multiplies by 10^4, which should be equivalent to the HHI definition.

Regards,

Owen

Re: Calculate sum or squares of column percentage values

lbendlin — Sun, 07 Feb 2021 14:12:23 GMT

Here's your DAX. Pretty sure this can be optimized.

HHI =

var a = sumx('Market Data','Market Data'[Revenue - USD])

var v = ADDCOLUMNS(VALUES('Market Data'[Vendor]),"V",'Market Data'[Vendor])

var s = ADDCOLUMNS(v,"Share",Int(100*sumx(filter('Market Data','Market Data'[Vendor]=[V]),'Market Data'[Revenue - USD])/a))

return sumx(s,[Share]*[Share])

Here's a shorter version:

HHI2 =

var a = sumx('Market Data','Market Data'[Revenue - USD])/100

var v = SUMMARIZE('Market Data','Market Data'[Vendor],"Share",int(sum('Market Data'[Revenue - USD])/a))

return sumx(v,[Share]*[Share])

Re: Calculate sum or squares of column percentage values

Anonymous — Sun, 07 Feb 2021 06:09:32 GMT

Thank you OwenAuger !

This is very close to what I'm looking to do. It calculates HHI correctly for the Market and SubSegment categories. The only thing it isn't doing is filtering by year. If I use Year for Columns in matrix visual, I get identical values for both 2018 and 2019. These are aggregate values for all years.

While I can add a slicer, I'd like to see the year-over-year change. Is there a way to do both rowwise and columnwise filtering?

Re: Calculate sum or squares of column percentage values

OwenAuger — Sun, 07 Feb 2021 07:29:04 GMT

You're welcome Anonymous.

Are you using the Year column from the 'Market Data' table (from the auto-generated date hierarchy)?

If I use that column at my end, the filtering by Year appears to work as expected.

Updated file attached.

Regards,

Owen

Re: Calculate sum or squares of column percentage values

lbendlin — Sun, 07 Feb 2021 14:10:21 GMT

OwenAuger Note that the HHI definition expects integer values for the individual market share.

Re: Calculate sum or squares of column percentage values

Anonymous — Sun, 07 Feb 2021 16:57:52 GMT

D'oh! That's embarassing.
Your solution does just what I needed. Thank you so much for your help.

Re: Calculate sum or squares of column percentage values

Anonymous — Sun, 07 Feb 2021 19:23:17 GMT

I believe HHI expects market share percentages multiplied by 100 (e.g., .234 = 23.4) but not necessarily integers. This becomes important when you have lots of small providers with less than 1% market share.

Re: Calculate sum or squares of column percentage values

lbendlin — Sun, 07 Feb 2021 19:59:28 GMT

Re: Calculate sum or squares of column percentage values

Anonymous — Sun, 07 Feb 2021 20:26:28 GMT

I understand your point, but the phrase "as a whole number, not a decimal" is misleading. It really means that percentages are multiplied by 100. So again, 0.234 would be represented as 23.4, not 23.
I work with markets that have hundreds of vendors. Restricting in put to whole numbers would result in a total of more than 100% as well as many vendors with less than 0.5% listed as zero.

Re: Calculate sum or squares of column percentage values

lbendlin — Sun, 07 Feb 2021 20:38:43 GMT

That's what the definition says that you pointed to, and it is also mentioned explicitly in Wikipedia.

"The Herfindahl Index (H) ranges from 1/N to one, where N is the number of firms in the market. Equivalently, if percents are used as whole numbers, as in 75 instead of 0.75, the index can range up to 100², or 10,000."

They will have had a reason for this. It will never result in more than 100%, but yes, it will filter out anyone with less than 1% share. (or less than 0.5% if you interpret "whole number" and "rounded" rather than "rounded down").