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 Tabl...
  • 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') )
    )
)
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