Forum Discussion
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_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")
))
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
6 Replies
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]))
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.
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
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
