Forum Discussion

OAMKEM1's avatar
OAMKEM1
Regular Visitor
2 years ago
Solved

Calculate consumption ratio with blank feilds

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's avatar
    Anonymous
    2 years ago

    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.

7 Replies

  • OAMKEM1's avatar
    OAMKEM1
    Regular Visitor
    2 years ago

    small illustation of the challange 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      OAMKEM1
      Regular Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.