Ranking by Total Sum, then Creating Cumulative Running Total to determine coverage %

[Anonymous]

Hi All - 

I have been trying to understand what I am doing wrong for several days and am struggling.

I built a simple dummy data table to show my general approach:

Item  Group Value Date

A	A	8	Saturday, January 1, 2022
B	B	4	Saturday, January 1, 2022
C	C	19	Saturday, January 1, 2022
D	A	45	Sunday, January 1, 2023
E	B	71	Friday, January 6, 2023
F	C	84	Wednesday, January 11, 2023
G	A	70	Monday, January 16, 2023
H	B	58	Saturday, January 21, 2023
I	C	38	Thursday, January 26, 2023
J	A	29	Tuesday, January 31, 2023
K	B	62	Sunday, February 5, 2023
L	C	44	Friday, February 10, 2023
M	A	91	Wednesday, February 15, 2023
N	B	77	Monday, February 20, 2023
O	C	16	Saturday, February 25, 2023
P	A	81	Thursday, March 2, 2023
Q	B	19	Tuesday, March 7, 2023
R	C	10	Sunday, March 12, 2023
S	A	33	Friday, March 17, 2023
T	B	48	Wednesday, March 22, 2023
U	C	14	Monday, March 27, 2023
V	A	47	Saturday, April 1, 2023
W	B	91	Thursday, April 6, 2023
X	C	81	Tuesday, April 11, 2023
Y	D	19	Sunday, April 16, 2023
Z		41	Friday, April 21, 2023

From this Dummy Table, it is my intention to take a filtered Group Sum, Rank that Group Sum by Size relative to Group, then create a cumulative sum by order of ranking (eventually establishing what categories by order of largest to smallest I would need to include to select 70% of the population, then the next 20%, and then the final 10%).

I have built the following measures so far:

SegmentSpendLast6MonthsExcludingSamples =

CALCULATE(

    SUM('Sheet1'[Value]),

    FILTER(

        Sheet1,

        YEAR('Sheet1'[Date]) >= 2023 &&

        NOT(ISBLANK('Sheet1'[Group]))

    )

)

SegmentRank = RANKX(ALL('Sheet1'[Group]), [SegmentSpendLast6MonthsExcludingSamples], , DESC)

SegmentCategory =

VAR SegmentSpend = [SegmentSpendLast6MonthsExcludingSamples]

VAR CurrentSegmentRank = [SegmentRank]

VAR CurrentGroup = MAX('Sheet1'[Group])

VAR CumulativeRank =

SUMX(

    FILTER(

    ALL('Sheet1'),

    'Sheet1'[Group] = CurrentGroup &&

    [SegmentRank]<=CurrentSegmentRank

    ),

    [SegmentSpendLast6MonthsExcludingSamples]

    )

VAR CumulativePercent = CumulativeRank / SegmentSpend

RETURN

CumulativeRank

My Table Visual currently shows:

SegmentSpendLast6MonthsExcludingSamples	Group	SegmentRank	SegmentCategory
426	B	1	426
396	A	2	396
287	C	3	287
19	D	4	19
		5

My intent is to show the following:

SegmentSpendLast6MonthsExcludingSamples	Group	SegmentRank	SegmentCategory	Cumulative %	Sampling Group
426	B	1	426	38%	Top 70%
396	A	2	822	73%	Top 70%
287	C	3	1109	98%	Next 20%
19	D	4	1128	100%	Final 10%
		5	1128	100%

I have yet to build a sampling group measure or a full cumulative % measure, but I know I need my cumulative sum measure/SegmentCategory to work first.

Any help would be much appreciated, I gone round and round with ChatGPT trying to understand what is missing here.

[lbendlin]

Why would Group A be in the Top 70% ?

[Anonymous]

Thanks for the response, the report user had asked for the grouping to be made to include the segment that triggers the 70% threshold to be met.