Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

do calculation on rowlevel and aggregate on summirized level

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 priceavg price LYVolumeVolume LYsales Sales LYSales this year's price LY's volume
item 110850004500500003600045000
item 23025500030015000075009000
item 3181850004800900008640086400
total19,3317150009600290000129900185600

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I got it to work with 

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

 

 

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I got it to work with 

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

 

 

 

Anonymous
Not applicable

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

 

/G

Anonymous
Not applicable

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
Super User
Super User

You can try

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

Hi, 

 

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

 

/g

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.