Removing cumulative frequency using power(m) query

Hi pwrbiadm 

This might run faster in DAX. However, place the following M code in a blank query to see the steps. If it's not fast enough we'll try another approach

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDsAgCAWvYlibFlC0ehXj/a/RNl01fZiUsCIzhM8YJLzrnUF7ThQpOGEbM834FvJfwRZCRULxBIH9Dw+vjPDm4Q0NY4xx0YxocWgTROtFf4qrh8Au7kMkIdy9bnn2nyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type number}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"B"}),
   
    col_ = List.RemoveLastN({0} & #"Replaced Value1"[B], 1),
    res_ = Table.FromColumns(Table.ToColumns(#"Replaced Value1") &  {col_}, {"A", "B","B_2"}),
    #"Changed Type1" = Table.TransformColumnTypes(res_,{{"B_2", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [B]-[B_2], type number),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,0,Replacer.ReplaceValue,{"B_2"})
in
    #"Replaced Value"

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

Hi pwrbiadm ,

 

Due to your large dataset, it is suggested to create a measure like so:

Measure =
VAR ThisRow_B =
    MAX ( [B] )
VAR LastRow_B =
    CALCULATE (
        MAX ( 'Query1 (2)'[B] ),
        FILTER (
            ALLSELECTED ( 'Query1 (2)' ),
            'Query1 (2)'[Index]
                = MAX ( 'Query1 (2)'[Index] ) - 1
        )
    )
RETURN
    SWITCH ( TRUE (), ThisRow_B >= LastRow_B, ThisRow_B - LastRow_B, ThisRow_B )

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.