cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtbWOW Occasional Visitor
Occasional Visitor

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

Re: Calculated Measure To Summarize Values in Another Table

Hi @jtbWOW

 

Try this revision...

 

=
CALCULATE (
    SUM ( 'Table2'[Net Sales Quantity] ),
    FILTER (
        'Table2',
        'Table2'[SO#] = SELECTEDVALUE ( 'Table1'[SO#] )
            && 'Table2'[PriceType] = SELECTEDVALUE ( 'Table1'[PriceTypeCode] )
    )
)
v-xjiin-msft Super Contributor
Super Contributor

Re: Calculated Measure To Summarize Values in Another Table

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 791 guests
Please welcome our newest community members: