DAX problem sum average

[bestevez]

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 multiplie total quantity (costeTotalTarLY) by average cost (Avg C.Und LY) and i need a sum of total. The total was be around 550.000 but is 3.109.896

Thanks for all

[Stachu]

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

[bestevez]

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

[Stachu · Accepted solution]

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

[bestevez]

Thanks, this solution is perfect. 

[Stachu]

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 that

[bestevez]

Thanks, but I can't think of a way to do the truth.