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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jtbWOW
New Member

Calculated Measure To Summarize Values in Another Table

Hello everyone. I am very inexperienced in DAX so the solution to my problem is probably simple, but I am having a hard time understanding DAX, primarily measures, the caculate & filter functions. I come from an Excel background and just don't seem to get it yet.

 

I have two tables in PowerPivot. Both tables have related columns [SO#] & [PriceType], but I am unable to just link the tables due to many-to-many relationships. I am attempting to create an explicit measure summarizing the net sales quantities column in my 2nd table based upon the filers applied to my first table. If I were just using Excel, SUMIFS would do the trick. I have tried several different formulas, but my pivot table results just end up totaling all of the net sales quantities in every row of my pivot table report. Please see the formula below. Any help would be greatly appreciated.

 

=CALCULATE(SUM('Table2'[Net Sales Quantity]),FILTER('Table2','Table2'[SO#]='Table1'[SO#]&&'Table2'[PriceType]='Table1'[PriceTypeCode]))

2 REPLIES 2
v-xjiin-msft
Solution Sage
Solution Sage

Hi @jtbWOW,

 

Could you please share us some sample data and its corresponding desired result if possible? So that we can get a right direction and make some proper tests rather than just guessing.

 

Thanks,
Xi Jin.

Zubair_Muhammad
Community Champion
Community Champion

Hi @jtbWOW

 

Try this revision...

 

=
CALCULATE (
    SUM ( 'Table2'[Net Sales Quantity] ),
    FILTER (
        'Table2',
        'Table2'[SO#] = SELECTEDVALUE ( 'Table1'[SO#] )
            && 'Table2'[PriceType] = SELECTEDVALUE ( 'Table1'[PriceTypeCode] )
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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