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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors