Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Apply Measure with Criteria

Hello,

 

I have 4 specific measures that I would like to combine into one column by their respective asset.

 

I have 4 assets that are measured by efficiency differently, hence why I created 4 specific measures. The issue I have is I would like to see these on one chart by asset. Per below, I have a separate chart for efficiency since I can't find a way to tie it into my first chart.

 

Example Measures:

Melt 1 = CALCULATE((SUM(AM_PRODUCTION_RESULT[TtL Lbs]) / sum(AM_PRODUCTION_RESULT[Hrs])) / 600)
Melt2  = CALCULATE((SUM(AM_PRODUCTION_RESULT[TtL Lbs]) / sum(AM_PRODUCTION_RESULT[Hrs])) / 450)
 
 
 
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, you may try this measure.

    Efficiency = 
var _asset = MAX(AM_PRODUCTION_RESULT[ASSET])

return
IF(
    _asset = "Meltblown 01 60in Accurate",
    SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/600,
    IF(
        _asset = "Meltblown 02 45in Accurate",
        SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/450,
        IF(
            _asset = "Meltblown 04 40in Accurate",
            SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/400,
            IF(
                _asset = "Meltblown_05 60in Accurate",
                SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/600,
                IF(
                    _asset = "Meltblown_06 27in Accurate",
                    SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/270
                )
            )
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Yes, you can manage to achieve your requirement. You may create a measure as follows.

    DisplayColor = 
var _asset = MAX(AM_PRODUCTION_RESULT[ASSET])
return
SWITCH(
    TRUE(),
    CONTAINSSTRINGEXACT(_asset,"60"),1,
    CONTAINSSTRINGEXACT(_asset,"45"),2,
    CONTAINSSTRINGEXACT(_asset,"40"),3,
    CONTAINSSTRINGEXACT(_asset,"27"),4,
    0
)

     

    Then you can go to 'Format' area, click 'Conditional formatting' .

     

    Then you can click 'Advanced controls' and configure as follows.

     

    Result:

     

    Best Regards

    Allan

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, you may try this measure.

    Efficiency = 
var _asset = MAX(AM_PRODUCTION_RESULT[ASSET])

return
IF(
    _asset = "Meltblown 01 60in Accurate",
    SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/600,
    IF(
        _asset = "Meltblown 02 45in Accurate",
        SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/450,
        IF(
            _asset = "Meltblown 04 40in Accurate",
            SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/400,
            IF(
                _asset = "Meltblown_05 60in Accurate",
                SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/600,
                IF(
                    _asset = "Meltblown_06 27in Accurate",
                    SUM(AM_PRODUCTION_RESULT[TtL Lbs])/SUM(AM_PRODUCTION_RESULT[Hrs])/270
                )
            )
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This is great!! Thank you! 

       

      Is it possible to apply conditonal formatting in the same way? Based on those criteria by asset, each set has it's own condtional formatting for background color. 

       

       

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, Anonymous 

         

        Yes, you can manage to achieve your requirement. You may create a measure as follows.

        DisplayColor = 
var _asset = MAX(AM_PRODUCTION_RESULT[ASSET])
return
SWITCH(
    TRUE(),
    CONTAINSSTRINGEXACT(_asset,"60"),1,
    CONTAINSSTRINGEXACT(_asset,"45"),2,
    CONTAINSSTRINGEXACT(_asset,"40"),3,
    CONTAINSSTRINGEXACT(_asset,"27"),4,
    0
)

         

        Then you can go to 'Format' area, click 'Conditional formatting' .

         

        Then you can click 'Advanced controls' and configure as follows.

         

        Result:

         

        Best Regards

        Allan