Forum Discussion
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. 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?
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
5 Replies
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
AlBthat worked perfectly!!! Thank you!
JamesGordon Should be easy if you could attach a sample file as well.
AntrikshSharmahere is a link to the sample data (it wont let me attached a file to this thread)
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 ResultIt produces efficient internal queries with no bottleneck.
