cancel
Showing results 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

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 Calculate
• 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.No Division Type Character Mat. Group SUM QTY(LIMIT-L) SUM QTY(LIMIT-U) 1 1A A PPC 100 2 1A A DSC 100 3 1A A OPS 100 4 1A A NSP 100 5 1A A SAC 100 6 1A A GEN 100 1 1A A PPC 200 2 1A A DSC 200 3 1A A OPS 200 4 1A A NSP 200 5 1A A SAC 200 6 1A A GEN 200

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

 Dimentsion table - dGroup1OOT LIMIT-L LIMIT-U Division Type Character Mat. Group KeyT1 Tol1 KeyT2 Tol2 KeyT3 Tol3 KeyT4 Tol4 1A A PPC 100 >= 0 < 2 >= 2 < 5 1A A DSC 100 > 0 < 3 >= 3 < 5 1A A OPS 100 >= 0 < 1 >= 1 < 5 1A A NSP 100 >= 0 < 2 >= 2 < 5 1A A SAC 100 >= 0 < 1 >= 1 < 5 1A A GEN 100 >= 0 < 2 >= 2 < 5

 Dimentsion table - dGroup2OOT LIMIT-L LIMIT-U Division Type Character Mat. Group KeyT1 Tol1 KeyT2 Tol2 KeyT3 Tol3 KeyT4 Tol4 1A A PPC 200 >= 0 < 1 >= 1 < 3 1A A DSC 200 > 0 < 1 >= 1 < 3 1A A OPS 200 >= 0 < 1 >= 1 < 3 1A A NSP 200 >= 0 < 2 >= 2 < 3 1A A SAC 200 >= 0 < 1 >= 1 < 3 1A A GEN 200 >= 0 < 2 >= 2 < 3

example  of ftable with few Character

 Fact Table Division Type Area Center Material Mat. Group Quantity PPC DSC OPS 1A A 1 JOD 130338277 100 50 0 1 5 1A A 1 JOD 130338346 100 0 1 2 2 1A A 1 JOD 130339328 100 100 2 2 1 1A A 1 JOD 130339897 100 50 1 2 3 1A A 2 TOD 130339899 100 50 5 1 1 1A A 2 TOD 130338277 200 0 0 2 2 1A A 2 TOD 130338346 200 100 0 1 3 1A A 3 VOD 130339328 200 100 1 3 0 1A A 3 VOD 130339897 200 100 2 1 0 1A A 3 VOD 130339899 200 50 3 2 30
3 REPLIES 3
Microsoft Employee

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.
Helper I

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.No Division Type Character Mat. Group SUM QTY(LIMIT-L) SUM QTY(LIMIT-U) 1 1A A PPC 100 100 150 2 1A A DSC 100 250 0 3 1A A OPS 100 0 250 4 1A A NSP 100 5 1A A SAC 100 6 1A A GEN 100

Tolerance table

 Division Type Character Mat. Group KeyT1 LIMIT-L Tol-1 LIMIT-L KeyT2 LIMIT-L Tol-2 LIMIT-L KeyT1 LIMIT-U Tol-1 LIMIT-U KeyT2 LIMIT-U Tol-2 LIMIT-U 1A A PPC 100 >= 0 < 2 >= 2 <= 5 1A A DSC 100 > 0 < 3 >= 3 <= 5 1A A OPS 100 >= 0 < 1 >= 1 <= 5 1A A NSP 100 >= 0 < 2 >= 2 <= 5 1A A SAC 100 >= 0 < 1 >= 1 <= 5 1A A GEN 100 >= 0 <

 Fact Table Division Type Area Center Material Mat. Group Quantity PPC DSC OPS 1A A 1 JOD 130338277 100 50 0 1 5 1A A 1 JOD 130338346 100 0 1 2 2 1A A 1 JOD 130339328 100 100 2 2 1 1A A 1 JOD 130339897 100 50 1 2 3 1A A 2 TOD 130339899 100 50 5 1 1
Helper I

Hi

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

regards