Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bestevez
Helper I
Helper I

DAX problem sum average

Hi,

 

i have a issue about my dax measure. 

 

SUM( fac_compras[cantidad] ) * CALCULATE(  [avgCosteUnidad]; SAMEPERIODLASTYEAR(dim_date[Date] ) )


Captura.PNG
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
Stachu
Community Champion
Community Champion

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

Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

6 REPLIES 6
Stachu
Community Champion
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).

Column1Column2
A1
B2.5

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi, 

 

thanks overall

 

DateQuantityCostCoste Total
01/201851050
01/201851050
01/201832060
01/20183515
01/20185525
01/20185315
01/20185420
01/20185420
01/20185525
01/20195525
01/2019560,528
01/2019560,528
01/201951050
01/201951050
01/201932060
01/20193515
01/20195525
01/20195315
01/20195420
01/20195420
01/20195525
01/20195525
01/2019560,528
02/2019560,528
02/201950,52,5
02/201950,52,5
02/201951050
02/201951050
02/201932060
02/20193515
02/20195525
02/20195315
02/20195420
02/20195420
02/20195525
02/20195525
02/2019560,528
02/2019560,528
02/201950,52,5
02/201950,52,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
Community Champion
Community Champion

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

Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thanks, this solution is perfect. 

Stachu
Community Champion
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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.