Summarised Data

[JamesGordon]

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. I am looking to create a table to summarise the following information, Stock Number, Lowest Invoice number that relates to that stock number, the Invoice date that relates to that invoice number, and then add up all sales and cost values for that stock number.

Below is a screenshot of what i am trying to achieve which hopefully makes sense? The top table is the orignal data for two items and the lower table is what i am trying to achieve?

I have tried numerous DAX combinations but no luck! Does anyone have any suggestions ideas?

[AntrikshSharma]

@JamesGordon - Try this.. the file is attached below my signature. Let me know if it works for you.

New table =
VAR MinInvoicePerStock =
    SUMMARIZECOLUMNS ( James[Stock No], "MinInv", MIN ( James[Inv No] ) )
VAR MaintainLineage =
    TREATAS ( MinInvoicePerStock, James[Stock No], James[Inv No] ) 
    -- By using TREATAS we inforce a data lineage that treats newly 
    -- added virtual column MinInv as a part of the model
VAR Result =
    CALCULATETABLE ( James, MaintainLineage )
RETURN
    Result

It produces efficient internal queries with no bottleneck.

[AlB · Accepted solution]

@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 

[JamesGordon]

@AlBthat worked perfectly!!! Thank you!

[JamesGordon]

@AntrikshSharmahere is a link to the sample data (it wont let me attached a file to this thread)

https://jamesgordon-my.sharepoint.com/:x:/g/personal/james_moss_jgordon_co_uk/EVYKstDyVzNOtMfBxaRZLw...

[AntrikshSharma]

@JamesGordon  Should be easy if you could attach a sample file as well.