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
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!
