Hi Experts,
I've been trying for a while now to implement a TOP N + Others (As in, show the share of sales for the top N brands but include a representation for the rest of the brands). I tried following this guide: Filtering the top products alongside the other products in Power BI but haven't been successful.
This is my main table and there are no relevant relations here:
| MONTH | MARKET | CHANNEL | CLUSTER PLAYER | MANUFACTURER | BRAND | FLAVOR | FLAVOR SEGMENT | CATEGORY | CALORIC CONT | PACKAGING | SHARE TYPE | SHARE VALUE |
| 01/set/22 | FQ 1 | CHAN 1 | Tayv | Lig | Mullock | COLA | COLAS | CSD | REG | PET 3/3,3L | SOS | 99014 |
| 01/ago/22 | FQ 1 | CHAN 1 | Tayv | Lig | Mullock | COLA | COLAS | CSD | D/L | REFPET | SOS | 81036 |
| 01/set/22 | FQ 2 | CHAN 1+2 | Tayv | Lig | Mullock | COLA | COLAS | CSD | D/L | PET 250 | SOS | 43058 |
| 01/set/22 | FQ 3 | CHAN 1+2 | Tayv | Lig | Ips | UVA | SABORES | CSD | REG | PET 400-599 | SOS | 83666 |
| 01/ago/22 | CTRY | CHAN 1 | Tayv | Lig | Ips | GUA | SABORES | CSD | REG | LS/SLS | SOS | 85450 |
| 01/set/22 | FQ 3 | CHAN 1+2 | Ilp | Cycrust | Ets | GUA | SABORES | CSD | REG | PET 250 | SOS | 25516 |
| 01/set/22 | FQ 3 | CHAN 1+2 | Tayv | Lig | Doib | COLA | COLAS | CSD | D/L | KS | SOS | 47614 |
| 01/ago/22 | FQ 4 | CHAN 1+2 | Tayv | Lig | Lagon | GRAPE FUSION | SABORES | CSD | D/L | LT 350-399 | SOS | 18808 |
| 01/set/22 | FQ 2 | CHAN 1+2 | Tayv | Lig | Lagon | GUA | SABORES | CSD | REG | LT 350-399 | SOS | 93411 |
The dashboard itself is always filtered by Category and Share Type and I want this Top N to be responsive to filtering on other visuals (ie: by manufacturer, by cluster player, etc)
The ranking measure I created following the guide isn't responsive and to be honest I don't think it's doing what I mean for ti to be doing.
Ranking Top X =
IF (
ISINSCOPE ( 'QuickFlash'[BRAND] ),
VAR TotalValue =
-- This is the same as the [MONTH Value] measure which I made explicit here.
CALCULATE (
SUM(QuickFlash[SHARE VALUE]),
AND(
MONTH(QuickFlash[MONTH]) = MONTH(MAX(DataMax[MONTH])),
YEAR(QuickFlash[MONTH]) = YEAR(MAX(DataMax[MONTH]))
)
)
RETURN
IF (
TotalValue > 0,
VAR VisibleBrands =
CALCULATETABLE (
VALUES ( 'QuickFlash' ),
ALLSELECTED ( 'QuickFlash'[BRAND] )
)
VAR Ranking =
RANKX (
VisibleBrands,
[MONTH Value],
Total Value
)
RETURN
Ranking
)
)
I didn't limit the number of ranking brands in this measure because I'm still trying to figure out what I'm doing wrong.
Anyway, at the moment, if I look at the brands and the ranking measure in a matrix I get:
- Some brands with repeat ranking values, even though they don't tie for value (Brand A, B and C are ranked as first, but Brand A has a Month Value of 172k while B has 188k and C has 45k;
- Some numbers that just don't make sense, as if it's not really aggregating by brand (ie: Brand D is ranked 1045 even though there are less than 200 brands total (less than 100 with the filters being used)
Sample data can be found here in excel format: Sample Data
