Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]))
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.
Hi @jtbWOW
Try this revision...
= CALCULATE ( SUM ( 'Table2'[Net Sales Quantity] ), FILTER ( 'Table2', 'Table2'[SO#] = SELECTEDVALUE ( 'Table1'[SO#] ) && 'Table2'[PriceType] = SELECTEDVALUE ( 'Table1'[PriceTypeCode] ) ) )
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |