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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
_google
Helper I
Helper I

4# Super help :DAX Calcuate with complex criteria ( equivalent to SUMIFS)

 

Hi Experts,

 

Reaching out again with some complex scenario, hope some genious will help and hopefully i be clear in my problem defination.

To cut long story short, i have this complex excel source file( 25 mb- 98Col,20k row) on which based on tolerance criteria an analysis is to be done to fine split of quantity against "Character" actual values for material.

 

  • How it is done now by me using,Excel SUMIFS with nested IF to index Tolerance limits to sum quantity in fact table for associated "Character"
  • I am trying with powerpivot (before going powerbi) since current excel is overloaded with fomula thus stability issue. Have reached limit of my knoweldge of DAX CalculateSmiley Indifferent
  • Basically "Character" remains the same as material moves from Mat. Group to another ( ie 100 >200 >>)

am lookng for example idea even for on or two "character" on how this can be solved with right DAX method

 

Required output

Requrired (PIVOT TABLE)    
Sr.NoDivisionTypeCharacterMat. GroupSUM QTY(LIMIT-L)SUM QTY(LIMIT-U)
11AAPPC100  
21AADSC100  
31AAOPS100  
41AANSP100  
51AASAC100  
61AAGEN100  
11AAPPC200  
21AADSC200  
31AAOPS200  
41AANSP200  
51AASAC200  
61AAGEN200  

 

 

------------------------------------------------ source table-------------------------

 

Dimentsion table - dGroup1OOTLIMIT-L LIMIT-U
DivisionTypeCharacterMat. GroupKeyT1Tol1KeyT2Tol2 KeyT3Tol3KeyT4Tol4
1AAPPC100>=0<2 >=2<5
1AADSC100>0<3 >=3<5
1AAOPS100>=0<1 >=1<5
1AANSP100>=0<2 >=2<5
1AASAC100>=0<1 >=1<5
1AAGEN100>=0<2 >=2<5

 

Dimentsion table - dGroup2OOTLIMIT-L LIMIT-U
DivisionTypeCharacterMat. GroupKeyT1Tol1KeyT2Tol2 KeyT3Tol3KeyT4Tol4
1AAPPC200>=0<1 >=1<3
1AADSC200>0<1 >=1<3
1AAOPS200>=0<1 >=1<3
1AANSP200>=0<2 >=2<3
1AASAC200>=0<1 >=1<3
1AAGEN200>=0<2 >=2<3

 

example  of ftable with few Character

Fact Table        
DivisionTypeAreaCenterMaterialMat. GroupQuantityPPCDSCOPS
1AA1JOD13033827710050015
1AA1JOD1303383461000122
1AA1JOD130339328100100221
1AA1JOD13033989710050123
1AA2TOD13033989910050511
1AA2TOD1303382772000022
1AA2TOD130338346200100013
1AA3VOD130339328200100130
1AA3VOD130339897200100210
1AA3VOD130339899200503230
3 REPLIES 3
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @_google,

 

I have no idea about the expected result. How to calculate these results? Can you share how you did in Excel? What are the relationships between these tables? How to identify the Limit-L and Limit-U?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jiascu-msft

 

Hope this help!

 

This is how i manage it in excel using SUMIFS

 

=IFERROR(SUMIFS($H$4:$H$13,$G$4:$G$13,$F32,INDEX($I$4:$K$13,,MATCH($E32,$I$3:$K$3,0)),$F17&$G17,INDEX($I$4:$K$13,,MATCH($E32,$I$3:$K$3,0)),$H17&$I17),"")

 

 for lower limit

 SUMIFS ( ftable(Quantity), ftable(Mat. Group),Ouput table(Mat. Group), ftable ( index & match for "Character" range), Tolerance Table (KeyT1 LIMIT-L&Tol-1 LIMIT-L))

 for upper limit

 SUMIFS ( ftable(Quantity), ftable(Mat. Group),Ouput table(Mat. Group), ftable ( index & match for "Character" range), Tolerance Table (KeyT1 LIMIT-U&Tol-1 LIMIT-U))

 

For example : 

Total SUM Quantity for Mat. Group 100 is = 250 unit  , thus for Character - PPC  the Low  = 100 unit ( sum of material 130338277,130339897 fall in low limit)  and Upper = 150 ( remaining material fall in high limit).

 

Output table     
       
Sr.NoDivisionTypeCharacterMat. GroupSUM QTY(LIMIT-L)SUM QTY(LIMIT-U)
11AAPPC100100150
21AADSC1002500
31AAOPS1000250
41AANSP100  
51AASAC100  
61AAGEN100  

      

Tolerance table

Division

TypeCharacterMat. GroupKeyT1 LIMIT-LTol-1 LIMIT-LKeyT2 LIMIT-LTol-2 LIMIT-L KeyT1 LIMIT-UTol-1 LIMIT-UKeyT2 LIMIT-UTol-2 LIMIT-U
1AAPPC100>=0<2 >=2<=5
1AADSC100>0<3 >=3<=5
1AAOPS100>=0<1 >=1<=5
1AANSP100>=0<2 >=2<=5
1AASAC100>=0<1 >=1<=5
1AAGEN100>=0<      

        

Fact Table

         
DivisionTypeAreaCenterMaterialMat. GroupQuantityPPCDSCOPS
1AA1JOD13033827710050015
1AA1JOD1303383461000122
1AA1JOD130339328100100221
1AA1JOD13033989710050123
1AA2TOD13033989910050511
_google
Helper I
Helper I

Hi

 

Is it difficult scenario to manage with DAX or it needs to be done differently

 

regards

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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