Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 |
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
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 |
Hi
Is it difficult scenario to manage with DAX or it needs to be done differently
regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |