Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
I need your help, i'm trying to calculate consumption ration (Material consumption Quantiy / Production Quanitity). However, some of the feilds are blank..
so i need to create a dax to caluclate the total production with (ignoring) all blank feildsq
Solved! Go to Solution.
Hi @OAMKEM1 ,
The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a table named 'Table2'
Table 2 = SUMMARIZE('Table',[Profit Center],'Table'[Production Line],"Column",SUM('Table'[Production]))
2. Use the following DAX expression to create a column in 'Table'
Column = IF(ISBLANK('Table'[Production]),LOOKUPVALUE('Table 2'[Column],'Table 2'[Profit Center],'Table'[Profit Center],'Table 2'[Production Line],'Table'[Production Line]),[Production])
3.Use the following DAX expression to create a column in 'Table'
Consumption Ratio = IF(ISBLANK('Table'[ Consumption]),BLANK(),DIVIDE([ Consumption],[Column]))
4.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @OAMKEM1 ,
The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a column
Column = IF(NOT ISBLANK('Table'[Quantity]),SUM('Table'[Actual Productin]),BLANK())
2. Use the following DAX expression to create a measure
Consumption Ratio =
VAR _a = SUM('Table'[Quantity])
VAR _b = SUM('Table'[Column])
VAR _c = DIVIDE(_a,_b)
RETURN _c
3. Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you and really appreciate your prompt response..
This is good start what the measure you gave me it calulcated the (total Production) where what i'm looking for is to consider mutiple coulmns that are (Not Blank), (e.g. company Code, Date, Profit Center, Production Line...etc.
Can you help please?
regards,
Hi @OAMKEM1 ,
Try to modify the dax expression.
Consumption Ratio =
VAR _a = SUM('Table'[Quantity])
VAR _b = SUM('Table'[Column])
VAR _c = CALCULATE(DIVIDE(_a,_b),
NOT(ISBLANK('Table'[Company Code])),
NOT(ISBLANK('Table'[Date])),
NOT(ISBLANK('Table'[Profit Center])),
NOT(ISBLANK('Table'[Production Line PQ])))
RETURN _c
If my understanding is wrong, please provide pictures of the data and desired results.
Dear v-zhouwen-msft,
I really appreciate your effort, unfortunately for some reason it's not working the measure given it’s given the consumption ratio only for the (Total Company) similar to screenshot below (20.22),
What I need is to give me the consumption ratio for each Date, Company Code, Profit Center, Production Line, Account, material…etc.
In the below I have made a similar data set, hopefully it will clarify my issue
Company Name | Account | Profit Center | Material | Order | Consumption | Production Line | Production |
Utility Experts |
| P-1A |
|
|
| Plant 1A | 1000 |
Utility Experts |
| P-2A |
|
|
| Plant 2A | 800 |
Utility Experts |
| P-3A |
|
|
| Plant 2A | 900 |
The Chemists |
| P-1B |
|
|
| Plant 1B | 500 |
The Chemists |
| P-1B |
|
|
| Plant 1B | 100 |
The Chemists |
| P-1B |
|
|
| Plant 1B | 212 |
The Chemists |
| P-3B |
|
|
| Plant 2B | 223 |
The Chemists |
| P-3B |
|
|
| Plant 2B | 4343 |
The Chemists |
| P-2B |
|
|
| Plant 2B | 5488 |
The Chemists |
| P-2B |
|
|
| Plant 2B | 4543 |
Utility Experts | Acc. 1 | P-1A | Water | Order 1 | 500 | Plant 1A |
|
Utility Experts | Acc. 1 | P-1A | Water | Order 2 | 500 | Plant 1A |
|
Utility Experts | Acc. 1 | P-2A | Water | Order 2 | 440 | Plant 2A |
|
Utility Experts | Acc. 2 | P-1A | Gas | Order 1 | 200 | Plant 1A |
|
Utility Experts | Acc. 2 | P-1A | Gas | Order 1 | 200 | Plant 1A |
|
Utility Experts | Acc. 2 | P-2A | Gas | Order 2 | 176 | Plant 2A |
|
Utility Experts | Acc. 2 | P-2A | Gas | Order 2 | 176 | Plant 2A |
|
Utility Experts | Acc. 1 | P-3A | Water | Order 3 | 360 | Plant 2A |
|
The Chemists | Acc. 1 | P-1B | Water | Order 1 | 812 | Plant 1B |
|
The Chemists | Acc. 1 | P-1B | Water | Order 2 | 812 | Plant 1B |
|
The Chemists | Acc. 2 | P-1B | Gas | Order 1 | 974 | Plant 1B |
|
The Chemists | Acc. 2 | P-1B | Gas | Order 2 | 974 | Plant 1B |
|
The Chemists | Acc. 2 | P-2B | Water | Order 1 | 11,034 | Plant 2B |
|
The Chemists | Acc. 2 | P-2B | Water | Order 2 | 11,034 | Plant 2B |
|
The Chemists | Acc. 2 | P-2B | Gas | Order 1 | 13,241 | Plant 2B |
|
The Chemists | Acc. 1 | P-2B | Gas | Order 2 | 13,241 | Plant 2B |
|
The Chemists | Acc. 1 | P-3B | Water | Order 1 | 3,653 | Plant 2B |
|
The Chemists | Acc. 2 | P-3B | Water | Order 2 | 3,653 | Plant 2B |
|
The Chemists | Acc. 2 | P-3B | Gas | Order 1 | 4,383 | Plant 2B |
|
The Chemists | Acc. 1 | P-3B | Gas | Order 2 | 4,383 | Plant 2B |
|
Regards
Further Illustration to the below
regards,
Hi @OAMKEM1 ,
The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a table named 'Table2'
Table 2 = SUMMARIZE('Table',[Profit Center],'Table'[Production Line],"Column",SUM('Table'[Production]))
2. Use the following DAX expression to create a column in 'Table'
Column = IF(ISBLANK('Table'[Production]),LOOKUPVALUE('Table 2'[Column],'Table 2'[Profit Center],'Table'[Profit Center],'Table 2'[Production Line],'Table'[Production Line]),[Production])
3.Use the following DAX expression to create a column in 'Table'
Consumption Ratio = IF(ISBLANK('Table'[ Consumption]),BLANK(),DIVIDE([ Consumption],[Column]))
4.Final output
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
small illustation of the challange
User | Count |
---|---|
52 | |
35 | |
19 | |
15 | |
14 |
User | Count |
---|---|
94 | |
72 | |
30 | |
22 | |
14 |