Forum Discussion

gdssiqueira's avatar
gdssiqueira
Helper I
10 years ago
Solved

Subtract values from different rows

Hello,   I'm working on a project and there's something I need your help with. I need to display the difference between two values in different rows. Ideally, I'd need a visual that allowed me to d...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    Hi gdssiqueira

     

    1. Create a new table summarized:

     

    TableWorkSumm =
    SUMMARIZE (
        TableWork;
        TableWork[Date ];
        TableWork[Category ];
        "Result"; IF (
            CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" )
                - CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Refund" )
                = CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" );
            BLANK ();
            CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" )
                - CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Refund" )
        )
    )
     
    2. When you use the Column Result; Select Visual Filter to Result to Is not Blank and Applied
     
     
ImkeF's avatar
ImkeF
Community Champion
10 years ago

You can use this statement in the query editor:

 

let
    Source = YourTable,
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Type]), "Type", "Value", List.Min),
    Difference = Table.AddColumn(#"Pivoted Column", "Difference", each [Purchase]-[Refund]),
    AddType = Table.AddColumn(Difference, "Type", each "Purchase"),
    #"Merged Queries" = Table.NestedJoin(Source,{"Caterory", "Date", "Type"},AddType,{"Caterory", "Date", "Type"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Difference"}, {"Difference"})
in
    #"Expanded NewColumn"

 

As you see in the Pivoted-Column-step we're choosing the minimum value from dups on type. So you could also take the maximum instead if that fits better.