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') )
    )
)