Calculate function using Containsstring filter not working as expected

[Anonymous]

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_shrinkage_act' and I'm using two columns to derive a calculated measure, [Total_Hrs] and [Shrinkage_cat_code] . I break these down by day of week in the matrix tables below. 

I'm just trying to divide the Total Hrs column by itself with help of filters. The denominator needs to be category "Shift" and the numerator needs to be all the other category codes EXCEPT for "Shift" as it just adds itself (100%) to the overall sum which isn't helpful. 

What seems to work, but without filtering out "Shift" from numerator: Visual breaks if I deselect "Shift" in a slicer

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

What I expected to work, but it does some weird aggregation that's completely not helpful...

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

 

[v-cazheng-msft · Accepted solution]

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]

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]

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

Category	Value
Absence	12
Leave	4
Break	3
Shift	82

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. 

[Whitewater100]

Hi:

I understand. Can you try this link for report? There is a table with calculation as well, but most are measures. I hope this helps!

https://drive.google.com/file/d/1O81vtC1QOyE_hoiLrBttXw-6Ibr9X1uE/view?usp=sharing 

[Anonymous]

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. 

[Whitewater100]

Hi:

No problem. They are based on what you shared. You can have a variable in a calculated measure.

Measure Name

var shift = Table[Shift[ID]

return

the DAX referring to this variable.  Example only.

return

Do you have example data(Model-table-visual you want to create) that reflects all the nuances  to writing the DAX?