Forum Discussion

bestevez's avatar
bestevez
Icon for Helper I rankHelper I
7 years ago
Solved

DAX problem sum average

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
average
DAX
  • Stachu's avatar
    Stachu
    7 years ago

    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

6 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      bestevez
      Icon for Helper I rankHelper I
      7 years ago

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

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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

     

    • bestevez's avatar
      bestevez
      Icon for Helper I rankHelper I
      7 years ago

      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's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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