Forum Discussion

Daptoid's avatar
Daptoid
Frequent Visitor
5 years ago
Solved

Calculating difference between numbers in different rows, different columns with filters - HELP!

  Hi Power BI users. I am hoping for some assistance as I am stumped for a solution to my problem.   I have a table of fleet car use. The table contains various different cars. Entries are usuall...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Daptoid 

    Try to build calculated columns to achieve your goal.

    Firstly build a rank column.

    Rank = RANKX(FILTER('Table','Table'[Vehicle]=EARLIER('Table'[Vehicle])),'Table'[ KMS at start],,ASC)

    Then build Expection column as below.

    Expection = 
VAR _Vehicle = 'Table'[ KMS at start]
VAR _NewVehicle =
    CALCULATE (
        SUM ( 'Table'[KMS at end] ),
        FILTER (
            'Table',
            'Table'[Vehicle] = EARLIER ( 'Table'[Vehicle] )
                && 'Table'[Rank]
                    = EARLIER ( 'Table'[Rank] ) - 1
        )
    )
RETURN
    IF ( 'Table'[Rank] = 1, 0, _Vehicle - _NewVehicle )

    Result is as below.

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Daptoid 

 

Do you mean you have a table like above sample without the "Exception" coulmn which you want to have? You want to do it in M or DAX? Below is one in M, paste the code in Advanced Editor with a blank query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9NCsMgEAXgq4RZBzIzzkh0lwMUupcsEpN79Sw9WfwpxVjoRkTe5+OFANseiQ2M8H4NhJguhJrOeUKaGJnSOzov6mUelgesY4C4b4apGs1Ee0HmI55V3Fo4p0mwN+K5NfE42Ug1NoftT4v+aSn/k7je2Lv5bikbSo9r49hMXy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vehicle = _t, #"KMS at start" = _t, #"KMS at end" = _t, DateTimestart = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"KMS at start", Int64.Type}, {"KMS at end", Int64.Type}, {"DateTimestart", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DateTimestart", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Vehicle"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [
a= Table.AddIndexColumn([allrows],"Index",0,1),
b=Table.AddColumn(a,"Exception", each try [KMS at start] - a{[Index]-1}[KMS at end] otherwise 0 )][b]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Vehicle", "KMS at start", "KMS at end", "DateTimestart", "Exception"}, {"Vehicle", "KMS at start", "KMS at end", "DateTimestart", "Exception"})
in
    #"Expanded Custom"
Daptoid's avatar
Daptoid
Frequent Visitor
5 years ago

Thanks Anonymous I really appreciate this. I was thinking of dax solutions but will test this as an M Solution. Thanks again