Forum Discussion

Sofinobi's avatar
Sofinobi
Icon for Helper IV rankHelper IV
3 years ago
Solved

Summarized table from two tables

hello community
please if you can help me,

i have 2 tables : Product and Date
i want to create a summarized table that have a column from Product table (Product) and the (Month year from Date Table)
result like this image (every product in every month)

and then insert the measures to separate the values of each Product and Month.
Average.pbix 
thank you

  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    never use SUMMARIZE to add columns!

    OverStock Table = ADDCOLUMNS(SUMMARIZE(Sales_Tab,'Product'[Product],'Date'[Month Year]),"AVG Stock",'Measure'[AVG Stock Qte Month],"AVG Sales Qte R3M",'Measure'[AVG Sales Qte R3M],"AVG Price",'Measure'[AVG Price Month])
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    GENERATE (
    SUMMARIZE ( 'Sales_Tab', 'Product'[Product] ),
    ADDCOLUMNS (
        SUMMARIZE ( 'Date', 'Date'[Month Year], 'Date'[YearMonth Number] ),
        "Avg Stock",
            VAR _date =
                CALCULATE ( MAX ( 'Date'[Date] ) )
            VAR _mon =
                FORMAT (
                    MAXX (
                        FILTER ( ALL ( 'Date'[Date] ), [AVG Stock Qte Month] && 'Date'[Date] <= _date ),
                        'Date'[Date]
                    ),
                    "mmm yy"
                )
            RETURN
                CALCULATE ( 'Measure'[AVG Stock Qte Month], 'Date'[Month Year] = _mon, REMOVEFILTERS('Date') )
    )
)

12 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    never use SUMMARIZE to add columns!

    OverStock Table = ADDCOLUMNS(SUMMARIZE(Sales_Tab,'Product'[Product],'Date'[Month Year]),"AVG Stock",'Measure'[AVG Stock Qte Month],"AVG Sales Qte R3M",'Measure'[AVG Sales Qte R3M],"AVG Price",'Measure'[AVG Price Month])
    • Sofinobi's avatar
      Sofinobi
      Icon for Helper IV rankHelper IV
      3 years ago

      thank you so much wdx223_Daniel thats exactely what i want, i'll try to learn more about ADDCOLUMNS and SUMMARIZE. 
      please, just one more thing if you can; 
      the measure [AVG Stock Qte Month] dont show any value when there is not sales in that month, in this case, i want to show me the value of last month,
      this image

      in this image the product "AMLI 30" in May 22 was 929,00, i need the same value in June 22 
      thank you very very much

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Icon for Community Champion rankCommunity Champion
        3 years ago

         

        GENERATE (
    VALUES ( 'Product'[Product] ),
    VAR _max =
        CALCULATE ( MAX ( 'Sales_Tab'[CreationDate] ) )
    RETURN
        ADDCOLUMNS (
            CALCULATETABLE ( VALUES ( 'Date'[Month Year] ), 'Date'[Date] <= _max ),
            "Avg Stock",
                VAR _date =
                    CALCULATE ( MAX ( 'Date'[Date] ) )
                VAR _mon =
                    FORMAT (
                        MAXX (
                            FILTER ( ALL ( 'Date'[Date] ), [AVG Stock Qte Month] && 'Date'[Date] <= _date ),
                            'Date'[Date]
                        ),
                        "mmm yy"
                    )
                RETURN
                    CALCULATE ( 'Measure'[AVG Stock Qte Month], 'Date'[Month Year] = _mon )
        )
)
        AVG Stock Qte Month = AVERAGEX(Sales_Tab,Sales_Tab[OldLogicalQuantity])

         

  • Sofinobi's avatar
    Sofinobi
    Icon for Helper IV rankHelper IV
    3 years ago

    hi all,
    finnaly i find a sollution for my table, two expressions give the correct values but it still one expression doesn't show any value

    do you have any idea what is the problem?
    average3.pbix 
    thank you

    • DimaMD's avatar
      DimaMD
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Hi Sofinobi I faced the same problem solving your problem. Let's try to figure out what is wrong with you.
      What do you want to see the correct total when you calculate ( [AVG Stock Qte Month]-[AVG Sales Qte Month] ) * [AVG Price month],
      Can you provide your expected result?

    • Sofinobi's avatar
      Sofinobi
      Icon for Helper IV rankHelper IV
      3 years ago

      hi DimaMD thank you for your answer,

      but it isn't what i'm looking for
      i need a separate table, not a matrix (or visual)
      i need that for my final result, that i will calculate a measure = 
      ( [AVG Stock Qte Month]-[AVG Sales Qte Month] ) * [AVG Price month]
      because actualy when i do this calculation, my Averages measures calculate all the values in a column, not averages of each product separately
      thats why i need a table to separate them
      thank you