Forum Discussion
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...
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 - 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.