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

[_google]

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

[v-jiascu-msft]

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

[_google]

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

[_google]

Hi

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

regards