topic Re: do calculation on rowlevel and aggregate on summirized level in DAX Commands and Tips

do calculation on rowlevel and aggregate on summirized level

Anonymous — Mon, 27 Jun 2022 10:24:35 GMT

Hi,

I have a calculation on Item level and I want to sum the result when more then one item is selected

"simple" example below:

	avg price	avg price LY	Volume	Volume LY	sales	Sales LY	Sales this year's price LY's volume
item 1	10	8	5000	4500	50000	36000	45000
item 2	30	25	5000	300	150000	7500	9000
item 3	18	18	5000	4800	90000	86400	86400
total	19,33	17	15000	9600	290000	129900	185600

I am looking for a dax code to "Sales this year's price LY's volume".

Currently it is

Sales this year's price LY's volume = [avg price] * [Volume LY]

It is correct on row level. On the total row I would like it to show the sum (140400) instead of 185600 (red in table).

the source table contains sales on order level so adding a calculated column would mean I would first have to create a new consolidated table and that a lot of details currently available would be lost.

any advice?

Re: do calculation on rowlevel and aggregate on summirized level

johnt75 — Mon, 27 Jun 2022 10:31:41 GMT

You can try

Sales this years price LY volume = SUMX( 'Products', [avg price] * [Volume LY])

Re: do calculation on rowlevel and aggregate on summirized level

Anonymous — Mon, 27 Jun 2022 11:11:57 GMT

Hi,

I tried but it gives me the sum of all items (30 000+) not just the three selected.

Re: do calculation on rowlevel and aggregate on summirized level

johnt75 — Mon, 27 Jun 2022 11:20:35 GMT

Try

Sales this years price LY volume = IF ( ISINSCOPE ( 'Products'[item code] ), SUMX ( 'Products', [avg price] * [Volume LY] ), CALCULATE ( SUMX ( 'Products', [avg price] * [Volume LY] ), ALLSELECTED ( 'Products' ) ) )

Re: do calculation on rowlevel and aggregate on summirized level

Anonymous — Mon, 27 Jun 2022 11:46:54 GMT

Thanks again.

I am getting a error:

"Failed to resolve name 'ISINSCOPE'. It si not a valid table, variable, or function name."

I am sure I did not misspell...

any ideas?

Re: do calculation on rowlevel and aggregate on summirized level

Anonymous — Mon, 27 Jun 2022 11:52:38 GMT

... I am running in direct querry. That is why it does not work.

Re: do calculation on rowlevel and aggregate on summirized level

Anonymous — Wed, 29 Jun 2022 08:56:32 GMT

I got it to work with

sumx( values( Sales[ItemID]), [avg price] * [Volume LY] )