Morning
Hope somebody might be able to help me with this.
I'm trying to dynamically calculate the HHI for some data, which will vary depending on the values selected in slicers.
I've got about 40k rows of data, one for each product. Each product has 6 descriptors and I need a slicer for each descriptor. A smaller example of this is given in the table below.
| Company | Product Category | Colour | Size | Sales Volume |
| A | 1 | Green | Little | 30 |
| A | 2 | Blue | Big | 130 |
| A | 14 | Red | Medium | 10 |
| A | 53 | Yellow | Little | 280 |
| B | 21 | Puce | Big | 121 |
| B | 16 | Green | Medium | 178 |
| B | 5 | Green | Little | 150 |
| C | 33 | Blue | Big | 25 |
| C | 14 | Red | Medium | 0 |
| C | 17 | Yellow | Little | 15 |
| D | 41 | Puce | Big | 25 |
| D | 25 | Green | Medium | 10 |
| D | 24 | Green | Little | 5 |
| D | 5 | Blue | Big | 5 |
| E | 21 | Red | Medium | 25 |
| E | 37 | Yellow | Little | 10 |
| etc | | | | |
I can easily get the report to dynamically calculate shares of sales depending on the slicer selections, and HHI is the sum of the squares of these shares, so I can do the first part of the calculation also. I have a measure that calculates "Share" and another for "HHI Calc".
| Company | Sales Volume | Share | HHI Calc |
| A | 450 | 44.2% | 19.5% |
| B | 449 | 44.1% | 19.4% |
| C | 40 | 3.9% | 0.2% |
| D | 45 | 4.4% | 0.2% |
| E | 35 | 3.4% | 0.1% |
The HHI Calc dynamically calculates ((Sales / Total Sales) * (Sales / Total Sales)).
What I want to be able to do is display a card that shows the Sum of HHI Calc - in this example 39.4%.
Anybody have any ideas?
