cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX problem sum average

Hi,

i have a issue about my dax measure.

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
1 ACCEPTED SOLUTION
Community Champion

try this

```Measure =
VAR __Summary =
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

Thank you for the kudos 🙂

6 REPLIES 6
Community Champion

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

Thank you for the kudos 🙂

Helper I

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

Community Champion

try this

```Measure =
VAR __Summary =
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

Thank you for the kudos 🙂

Helper I

Thanks, this solution is perfect.

Community Champion

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

Thank you for the kudos 🙂

Helper I

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