Forum Discussion
Count rows dynamically and aggregate
This post almost explains perfectly what I want: https://community.powerbi.com/t5/Desktop/Dynamic-Count-of-Rows/m-p/662950
The code proposed by parry2k in the post also works:
Count of Products =
VAR __currentProduct = SELECTEDVALUE( Table3[Product] )
RETURN
CALCULATE(
COUNTROWS( Table3 ),
ALLSELECTED( Table3 ),
Table3[Product] = __currentProduct
)The only issue is: My "product" column is a number from 0 to 5, and I need my "CountRows" measure (remember, it has to be a measure!!!) to instead aggregate like this:
| Product | CountRows |
| 0 | 3 |
| 0 | 3 |
| 0 | 3 |
| 1 | 6 |
| 1 | 6 |
| 1 | 6 |
| 2 | 7 |
| 3 | 8 |
| 4 | 9 |
| 5 | 11 |
| 5 | 11 |
As you can see, there are 3 products = 0, so "countrows" is 3 for each value with "0" in product column.
Then there are 3 products = 1 in the product column, so "countrows" is 6 now, because we aggregate the countrows from product 0 with the countrows of product 1. We continue to aggregate based on the previous product value, as you can see in the table above.
How do I make this as a measure?
Hi, msuser48 ;
Try it.
Count of Products = VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) RETURN IF(HASONEVALUE(Table3[Product]), CALCULATE( COUNTROWS( Table3 ), ALLSELECTED( Table3 ), Table3[Product] <= __currentProduct ),COUNTROWS('Table3'))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi, msuser48 ;
Try it.
Count of Products = VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) RETURN IF(HASONEVALUE(Table3[Product]), CALCULATE( COUNTROWS( Table3 ), ALLSELECTED( Table3 ), Table3[Product] <= __currentProduct ),COUNTROWS('Table3'))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.msuser48 - This what you're going for?
ChrisMendozaalmost.
But as it was also requested in the post i referred to, this has to work when filters are applied, but it currently does not.Example table with no filters:
Id Date Product Running Total 1 2017-01-01 0 3 2 2018-01-01 0 3 3 2019-01-01 0 3 4 2017-01-01 1 6 5 2020-01-01 1 6 6 2021-01-01 1 6 7 2021-01-01 2 7 8 2022-01-01 3 8 9 2022-01-01 4 9 10 2017-01-01 5 11 11 2022-01-01 5 11 If we use a slicer filter for "Date", and set this slicer to be between the range 2018-01-01 to 2023-01-01, we should get this for our "Running Total" measure (as the dates highlighted in bold above are removed):
Id Date Product Running Total 2 2018-01-01 0 2 3 2019-01-01 0 2 5 2020-01-01 1 4 6 2021-01-01 1 4 7 2021-01-01 2 5 8 2022-01-01 3 6 9 2022-01-01 4 7 11 2022-01-01 5 8 Again, running total should be a measure, as I understand it will only work with a measure.
