Forum Discussion
Cumulative count only if value appear one by another
Hello,
I have a problem with some calculated column...
I would like to acheive CUMULATIVE count value but when previous group was different then start counting from the begining.
Expected Value:
| GROUP | EXPECTED VALUE | INDEX |
| A | 1 | 1 |
| A | 2 | 2 |
| B | 1 | 3 |
| B | 2 | 4 |
| B | 3 | 5 |
| C | 1 | 6 |
| A | 1 | 7 |
| A | 2 | 8 |
Right now it is like that:
| GROUP | VALUE | INDEX |
| A | 1 | 1 |
| A | 2 | 2 |
| B | 1 | 3 |
| B | 2 | 4 |
| B | 3 | 5 |
| C | 1 | 6 |
| A | 3 | 7 |
| A | 4 | 8 |
My DAX measure:
CALCULATE ( COUNT(GROUP),
FILTER(ALL(TABLE),
GROUP = EARILER(GROUP),
INDEX <= EARLIER(INDEX)
)
Any advice will be very helpful for me!
Thanks
I can assure you that it requires recursive processing, so that the measure, if exists, is far beyond comprehension for normal users like you or me.
My solution evolves a helper column,
6 Replies
CNENFRNLCommunity Champion
I can assure you that it requires recursive processing, so that the measure, if exists, is far beyond comprehension for normal users like you or me.
My solution evolves a helper column,
- Fowmy
Super User
BIDeveloper_
How about a Power Query approach? Past this code on a New Query in the Advanced Edior and check the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgyxjOMoGzTMEsZyDLDK7DHM6yUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GROUP = _t, #" INDEX" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"GROUP", type text}, {" INDEX", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"GROUP"}, {{"All", each _, type table [GROUP=nullable text, #" INDEX"=nullable number]}, {"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each {1..[Count]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"All", "Count"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type) in #"Added Index"Really thank you but unfortunatelly I have to make it with DAX because my "GROUP" column is a calculated column etc...
