Forum Discussion
Solved
DAX problem sum average
Hi, i have a issue about my dax measure. SUM( fac_compras[cantidad] ) * CALCULATE( [avgCosteUnidad]; SAMEPERIODLASTYEAR(dim_date[Date] ) ) all is right, but the total multipli...
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
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
Thanks, this solution is perfect.