Calculate consumption ratio with blank feilds

[OAMKEM1]

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

[Anonymous]

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.

[OAMKEM1]

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,

[Anonymous]

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.

[OAMKEM1]

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

[OAMKEM1]

Further Illustration to the below 

regards,

[Anonymous · Accepted 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.

[OAMKEM1]

small illustation of the challange