Forum Discussion
DAX problem sum average
Hi,
i have a issue about my dax measure.
try this
Measure = VAR __Summary = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Calendar'[YearMonth] ), "Quantity CY", CALCULATE ( SUM ( 'Table'[Quantity] ) ), "Avg Cost LY", [Average Cost LY] ) RETURN SUMX ( __Summary, [Quantity CY] * [Avg Cost LY] )the total is alignes with your logic here
6 Replies
you need to use SUMX here, e.g. with SUMMARIZECOLUMNS (with whatever is in the rows before the numbers you show)
right now PowerBI adds all rows and then multiplies
you want it to multiply and then add rows, SUMX can do thatThanks, but I can't think of a way to do the truth.
Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
Column1 Column2 A 1 B 2.5 Hi,
thanks overall
Date Quantity Cost Coste Total 01/2018 5 10 50 01/2018 5 10 50 01/2018 3 20 60 01/2018 3 5 15 01/2018 5 5 25 01/2018 5 3 15 01/2018 5 4 20 01/2018 5 4 20 01/2018 5 5 25 01/2019 5 5 25 01/2019 56 0,5 28 01/2019 56 0,5 28 01/2019 5 10 50 01/2019 5 10 50 01/2019 3 20 60 01/2019 3 5 15 01/2019 5 5 25 01/2019 5 3 15 01/2019 5 4 20 01/2019 5 4 20 01/2019 5 5 25 01/2019 5 5 25 01/2019 56 0,5 28 02/2019 56 0,5 28 02/2019 5 0,5 2,5 02/2019 5 0,5 2,5 02/2019 5 10 50 02/2019 5 10 50 02/2019 3 20 60 02/2019 3 5 15 02/2019 5 5 25 02/2019 5 3 15 02/2019 5 4 20 02/2019 5 4 20 02/2019 5 5 25 02/2019 5 5 25 02/2019 56 0,5 28 02/2019 56 0,5 28 02/2019 5 0,5 2,5 02/2019 5 0,5 2,5 Then i need to calculate a 1 measure Average of Cost LY (01/2018)
Then i need to multiplie Quantity this year (01/2019) by Average of Cost LY (01/2018) an sum. This sum is error.THanks
try this
Measure = VAR __Summary = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Calendar'[YearMonth] ), "Quantity CY", CALCULATE ( SUM ( 'Table'[Quantity] ) ), "Avg Cost LY", [Average Cost LY] ) RETURN SUMX ( __Summary, [Quantity CY] * [Avg Cost LY] )the total is alignes with your logic here
