Cumulative sums that can be filtered

[Cogidubnus_Rex]

I am in need for some expertise, please, having been going at this for several days without success.

Consider the below table (as an image for better visuals then as a table to easily recreate the table contents):

I have	I have	I NEED AS A MEASURE	I have	I have	I have	I NEED AS A MEASURE
Business unit	Value	Cumulative value	Rank	Late?	Timeliness count	Cumulative on-time %	What I want (not as a column but as a measure as it needs to be dynamic and filterable)
A	$         1,234	$         1,234	1	Late	0	0%
A	$             325	$         1,559	2	Late	0	0%
A	$         2,521	$         4,080	3	Late	0	0%
A	$             236	$         4,316	4	Late	0	0%
B	$             875	$         5,191	5	Late	0	0%
B	$       24,563	$       29,754	6	Late	0	0%
B	$         7,455	$       37,209	7	Late	0	0%
B	$             546	$       37,755	8	On-time	1	12.5%	=SUM($F$3:F10)/COUNTA($D$3:D10)
B	$                 3	$       37,758	9	On-time	1	22.2%
C	$               46	$       37,804	10	On-time	1	30.0%
C	$         5,346	$       43,150	11	On-time	1	36.4%
D	$               57	$       43,207	12	On-time	1	41.7%
D	$             264	$       43,471	13	On-time	1	46.2%
D	$         7,546	$       51,017	14	On-time	1	50.0%
D	$               45	$       51,062	15	On-time	1	53.3%
D	$               73	$       51,135	16	On-time	1	56.3%
D	$             846	$       51,981	17	On-time	1	58.8%
D	$             486	$       52,467	18	On-time	1	61.1%
D	$         8,465	$       60,932	19	On-time	1	63.2%
E	$         4,658	$       65,590	20	On-time	1	65.0%
F	$         3,894	$       69,484	21	On-time	1	66.7%
F	$             679	$       70,163	22	On-time	1	68.2%
F	$                 6	$       70,169	23	On-time	1	69.6%

For 'Timeliness count', 0 means late, and 1 is on time
The cumulative columns are for illustrative purposes only, I don't want them as calculated columns as they won't show in tables properly when filtered.
My understanding is that these cumulative calculations need to be measures, because they need to be dynamic, as the user may filter by business unit (or one of several other variables not shown here), and the cumulative calculations need to filter according to whatever has been selected.
So, that is the first part, to recreate the calculations per the columns in black above, but as measures.
From there, we then need to add something like slicers or filters which enable the user to say, show me the invoices that will take me to, for example, 30% on-time timeliness.
In this case, the table would then show the top ten items only.
Equally, the user needs to be able to filter/ select for a cumulative value limit, e.g. $40,000.
In this case, a $40,000 limit selected would show the top ten items only.
If the cumulative value and cumulative on-time %s are created using measures, I guess we need a work-around as they can't just be dropped into a slicer, right?  If I'm wrong, great.  Otherwise, I'm stuck.

Please help!!
Thank you for reading and for any superstars able to help, thank you so much in advance.

@TomMartens, @v-juanli-msft

[Cogidubnus_Rex]

Unfortunately my image was deleted.  I'll try again!

[Cogidubnus_Rex]

@parry2k ?

[parry2k · Accepted solution]

@Cogidubnus_Rex try these measures if I understood correctly

Cumm Value = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Rank] <= MAX ( 'Table'[Rank] ) ) )

Cumm Rank = CALCULATE ( SUM ( 'Table'[Rank] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Rank] <= MAX ( 'Table'[Rank] ) ) )

Cumm Timeless = CALCULATE ( SUM ( 'Table'[Timeliess] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Rank] <= MAX ( 'Table'[Rank] ) ) )

% = DIVIDE ( [Cumm Timeless], [Cumm Rank] )

[Cogidubnus_Rex]

Thanks, @parry2k , great measures (and simple too).

The rank measure actually doesn't work, as it's summing the rank, which is a much higher number.  So, I added a column that Count  = "1", and swapped that into the beginning of the formula, as below, and now it always give a dynamic rank, regardless of any filters applied.

Cumm Rank = CALCULATE ( SUM ( 'Table'[Count] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[RANK] <= MAX ( 'Table'[RANK] ) ) )

The second part of the ask was around using these as slicers, but they work perfectly well in the filter pane, so I'm delighted.

One last things, however, is that the rank includes ties, at which point the cumulative calculations don't add rows (where the rank is tied).

I am not sure how to resolve this. Could you clarify, please? 🙂

Many thanks once again, fantastic answer.

[v-shex-msft]

HI @Cogidubnus_Rex ,

If you can please share some a pbix file with some dummy data and similar data structure, then we can further test and coding formula on it.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng