Forum Discussion

JamesGordon's avatar
JamesGordon
Helper II
5 years ago
Solved

Summarised Data

Hi, I have been trying to find a way to either create calculated columns with my data or a summarised table. We have unique stock numbers that have multiple transactions of invoices and credits...
  • AlB's avatar
    AlB
    5 years ago

    JamesGordon 

    This can also be done in Power Query (perhaps best) but here is a DAX solution. Create a new calculated table:

    New table = 
ADDCOLUMNS (
    FILTER (
        ALLEXCEPT ( Table1, Table1[Sales Val], Table1[Cost Val] ),
        VAR minInvNo_ =
            CALCULATE ( MIN ( Table1[Inv No] ), ALLEXCEPT ( Table1, Table1[Stock No] ) )
        RETURN
            Table1[Inv No] = minInvNo_
    ),
    "Sales Val", CALCULATE ( SUM ( Table1[Sales Val] ), ALLEXCEPT ( Table1, Table1[Stock No] ) ),
    "Cost val", CALCULATE ( SUM ( Table1[Cost Val] ), ALLEXCEPT ( Table1, Table1[Stock No] ) )
)

    See it all at work in the attached file. 

     

    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