Filtering and ADDCOLUMNS field containing a cumulative sum/total

[jechris]

I am trying to filter a table based on a cummulative sum/total which is calculated and added as an ADDCOLUMNS field.

I want find the smallest bin ("Table"[Bits]) with the) that contains a count above a specific number (in this example 30)

MEASURE= SELECTCOLUMNS(
    TOPN(1,
        TOPN(1,
            FILTER(
                ADDCOLUMNS(
                    'Table',
                    "CumSum",
                    CALCULATE (
                        SUM ( 'Table'[Count] ),
                        FILTER (
                            ALL ( 'Table'[Bits] ),
                            'Table'[Bits] <= MAX ( 'Table'[Bits] )
                        )
                    )
                ),
                [CumSum] > 30
            ),
            [CumSum],ASC
        ),
        [Bits],ASC
    ),
    "temp", 'Table'[Bits]
)

I want to be able to do this for any set of months. 

Month	Bits	Count
1	0	5
1	10	20
1	20	2
1	30	1
1	40	4
1	50	3
1	80	8
2	20	1
2	30	1
2	60	3
3	10	5
3	30	6

The expected result in this case (for all months/no filtering) is 20 Bits... since that is the first instance where the cumulative sum exceeds 30 (in this case 33 is the total).

I can't get this to work and the cumulative sum is never calculated for each bit bucket.

Please help

[Phil_Seamark]

Hi @jechris

I think this calculated measure might be close

Measure = 
VAR t = ADDCOLUMNS(
                    'Table',
                    "CumSum",
                    CALCULATE (
                        SUM ( 'Table'[Count] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Bits] <= EARLIER( 'Table'[Bits] )
                        )
                    )
				)
VAR x = FILTER(t,[CumSum]>30)
VAR z = MINX(x,[Bits])
RETURN z

[jechris]

@Phil_Seamark

Thank you for your solution. 
It seems to work well when no filters are applied but if I select a specific Month, say "2".


In this example I want to find the first Bit column where the cumulative sum is above 3. This should be 60 Bits in this case.

Is there a reason why these filters doesn't quite apply correctly to the page?

Thank you
Jens Christensen

[Phil_Seamark]

Hi @jechris

When you say "since that is the first instance where the cumulative sum exceeds 30 (in this case 33 is the total)."

How do you arrive at the value of 33?

[jechris]

Hi @Phil_Seamark

Sorry for not making that clear.

The cumulative sums for 20 bits is the sum of the counts in bold below from the table:
(5) + (20 + 5) + (2 + 1) = 33
That is the sum for each bin of bits for any all months.
(0 bit count) + (10 bit count) + (20 bit count) = cumulative sum at 20 bits.  

Month	Bits	Count
1	0	5
1	10	20
1	20	2
1	30	1
1	40	4
1	50	3
1	80	8
2	20	1
2	30	1
2	60	3
3	10	5
3	30	6

[Ashish_Mathur]

Hi @jechris,

So in a simpple card visual, do you want the answer to be 30?

[jechris]

Hi @Ashish_Mathur

Yes, correct.

Regards

Jens Christensen

[Ashish_Mathur]

Hi @jechris,

Sorry for taking so much time to respond.  I think i have a solution.  You may download my workbook from here.  While i have formulated this solution in MS Excel, one can do this in PowerBI desktop as well (or else you can directly also import this workbook to PowerBI desktop).

Hope this helps.