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

      thank you wdx223_Daniel  for your answer.
      your solution works, but it doesn't give me result in last months (image attached)

      on Dec 22 and Jan 23 i have result only for "MYCO"
      thank you very much

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Icon for Community Champion rankCommunity Champion
        3 years ago
        OverStock Table3 = 
GENERATE (
    SUMMARIZE ( 'Sales_Tab', 'Product'[Product] ),
    ADDCOLUMNS (
        VALUES ( 'Date'[Month Year] ),
        "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 )
    )
)