topic DAX problem sum average in DAX Commands and Tips

DAX problem sum average

bestevez — Mon, 19 Aug 2019 14:29:13 GMT

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

Re: DAX problem sum average

Stachu — Mon, 19 Aug 2019 15:41:10 GMT

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

Re: DAX problem sum average

bestevez — Tue, 20 Aug 2019 11:52:45 GMT

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

Re: DAX problem sum average

Stachu — Wed, 21 Aug 2019 14:05:06 GMT

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

Re: DAX problem sum average

bestevez — Thu, 22 Aug 2019 09:56:04 GMT

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

Re: DAX problem sum average

Stachu — Fri, 23 Aug 2019 07:43:03 GMT

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

Re: DAX problem sum average

bestevez — Fri, 23 Aug 2019 08:44:10 GMT

Thanks, this solution is perfect.