Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@ebalmeida , refer if this approch can help
Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE
Thanks, that worked like a charm.
@ebalmeida , refer if this approch can help
Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |