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.
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
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
@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
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?
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 |
Hi @jechris,
So in a simpple card visual, do you want the answer to be 30?
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.
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 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |