Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate function using Containsstring filter not working as expected

Hey Guys,   I'm sure I'm missing something small in the syntax here but I've tried this DAX measure about every way possible the past couple days without any luck.    I have a table called 'vw_sh...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous ,

     

    Maybe you could try this Measure.

    ACT% =
DIVIDE (
    CALCULATE (
        SUM ( vw_shrinkage_act[Total_Hrs] ),
        FILTER ( vw_shrinkage_act, vw_shrinkage_act[Shrinkage_cat_code] <> "SHIFT" )
    ),
    CALCULATE (
        SUM ( vw_shrinkage_act[Total_Hrs] ),
        CONTAINSSTRING ( vw_shrinkage_act[Shrinkage_cat_code], "SHIFT" )
    )
)

     

    Then the result will look like this:

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hi:

Instead of using ContainsString can you consider trying a measure like:

Share = 

= DIVIDE(SUMX(

vw_shrinkage_act[Total_Hrs]

),

SUMX(ALLSELECTED(

vw_shrinkage_act), [Total Hours]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    That doesn't quite work for what I'm trying to do. Example table below

    CategoryValue
    Absence12
    Leave4
    Break3
    Shift82


    Absence / Shift = 0.146 or 14.6%
    Leave / Shift = 0.048 or 4.8%
    Break / Shift = 0.036 or 3.6%
    All / Shift = 0.231 or 23.1%

     

    The problem I run into is that Shift / Shift = 100% and it gets added to the total values which just makes everything wrong. "Shift" is only there to be the denominator in the division, I need it excluded from the numerator which is where I got to the two measures I originally posted. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hey thanks for writing up these examples. We are super close here. The only issue left is that "Tot Shift" being MAX(value) isn't usable. My source table has many thousands of rows, there are many values for "Shift" being Summed and aggregated, so dividing by the maximum value of Shift yields incorrect math. I tried making some changes but I guess I can't use SELECTEDVALUE on a calculated measure.