topic Re: Summarized table from two tables in DAX Commands and Tips

Summarized table from two tables

Sofinobi — Wed, 25 Jan 2023 12:42:13 GMT

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

Re: Summarized table from two tables

DimaMD — Wed, 25 Jan 2023 14:13:35 GMT

Hi Sofinobi Look at the file, I hope I understood your task correctly

Re: Summarized table from two tables

Sofinobi — Thu, 26 Jan 2023 12:11:47 GMT

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

Re: Summarized table from two tables

Sofinobi — Thu, 26 Jan 2023 13:48:31 GMT

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

Re: Summarized table from two tables

DimaMD — Fri, 27 Jan 2023 07:48:07 GMT

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?

Re: Summarized table from two tables

wdx223_Daniel — Fri, 27 Jan 2023 08:19:32 GMT

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

Re: Summarized table from two tables

Sofinobi — Fri, 27 Jan 2023 12:45:10 GMT

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

Re: Summarized table from two tables

wdx223_Daniel — Mon, 30 Jan 2023 07:52:36 GMT

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

Re: Summarized table from two tables

Sofinobi — Mon, 30 Jan 2023 15:40:07 GMT

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

Re: Summarized table from two tables

wdx223_Daniel — Tue, 31 Jan 2023 00:06:16 GMT

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

Re: Summarized table from two tables

Sofinobi — Wed, 01 Feb 2023 09:41:35 GMT

thank you very much wdx223_Daniel that is perfect, you helped me a lot.
if i need yto add a column from Date table, is this syntax correct?

VALUES ( 'Date'[Month Year],'Date'[YearMonth Number] )

tank you so much my friend, i realy apreciate your help

Re: Summarized table from two tables

wdx223_Daniel — Thu, 02 Feb 2023 00:05:11 GMT

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

Re: Summarized table from two tables

Sofinobi — Sun, 12 Feb 2023 17:09:40 GMT

thank you very much wdx223_Daniel that's perfect, excately what i'm looking for,

you are the best my friend, thank you again