do calculation on rowlevel and aggregate on summirized level

[Anonymous]

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?

BR

G

[Anonymous · Accepted solution]

I got it to work with 

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

[Anonymous]

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

/G

[Anonymous]

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? 

/g

[johnt75]

You can try

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

[Anonymous]

Hi, 

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

/g

[johnt75]

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