I have a dataset that looks like so:
ID SALE | PRODUCT | REGION |
---|---|---|
SE_056 | AAA | NORTH |
XT-558 | XXX | NORTH |
8547Y | CCC | NORTH |
TY856 | XXX | NORTH |
D-895 | BBB | SOUTH |
ER5H | CCC | SOUTH |
5F6F-GD | CCC | SOUTH |
65-FFD | TTT | SOUTH |
56-YU | XXX | SOUTH |
I would like to create a table that will show me the subtotal (COUNT) for each product as well as their indivudal percentage and cumulative percentage like so:
PRODUCT | subtotal | Percentage | Cum Percentage |
---|---|---|---|
CCC | 3 | 0,33333333 | 0,333333333 |
XXX | 3 | 0,33333333 | 0,666666667 |
AAA | 1 | 0,11111111 | 0,777777778 |
BBB | 1 | 0,11111111 | 0,888888889 |
TTT | 1 | 0,11111111 | 1 |
I want this table to be sort by highest subtotal to lowest subtotal and that in that matter the cumulative percentage be as well calculated, PBI is not easy. I cannot find a way to get my table to be sort by highest subtotal and have the cum % calculated in that same order.
You can do this in a report visualisation with a few measures, to calculate number of sales, percentage of sales, product rank by sales (then product name), and cumulative sales percentage:
# Sales = COUNT ( Sales[ID SALE] )
% Sales =
DIVIDE (
[# Sales],
CALCULATE (
[# Sales],
REMOVEFILTERS ( Sales[PRODUCT] )
)
)
% Sales (Cumulative) =
VAR CurrentRank =
IF (
ISINSCOPE ( Sales[PRODUCT] ),
[Rank by Sales then Product],
DISTINCTCOUNT ( Sales[PRODUCT] )
)
RETURN
SUMX (
ALL ( Sales[PRODUCT] ),
IF (
[Rank by Sales then Product] <= CurrentRank,
[% Sales],
BLANK()
)
)
Rank by Product =
RANKX (
ALL ( Sales[PRODUCT] ),
FIRSTNONBLANK ( Sales[PRODUCT], 1 ),,
ASC,
Dense
)
Rank by Sales then Product =
RANKX (
ALL ( Sales[PRODUCT] ),
[# Sales] + ( 1 / [Rank by Product] ),,
DESC,
Dense
)
Sample output:
EDIT: You could do the % Sales (Cumulative) measure in on, and remove the 'Rank' measures, but it's significantly less legible:
% Sales (Cumulative) =
VAR CurrentRank =
IF (
ISINSCOPE ( Sales[PRODUCT] ),
RANKX (
ALL ( Sales[PRODUCT] ),
[# Sales] +
DIVIDE (
1,
CALCULATE (
RANKX (
ALL ( Sales[PRODUCT] ),
FIRSTNONBLANK ( Sales[PRODUCT], 1 ),,
ASC,
Dense
)
)
),,
DESC,
Dense
),
DISTINCTCOUNT ( Sales[PRODUCT] )
)
RETURN
SUMX (
ALL ( Sales[PRODUCT] ),
IF (
RANKX (
ALL ( Sales[PRODUCT] ),
[# Sales] +
DIVIDE (
1,
CALCULATE (
RANKX (
ALL ( Sales[PRODUCT] ),
FIRSTNONBLANK ( Sales[PRODUCT], 1 ),,
ASC,
Dense
)
)
),,
DESC,
Dense
) <= CurrentRank,
[% Sales],
BLANK()
)
)