Sum/Count Measure Output

[DemingPDCA]

I have created a measure that tells me if a "cell" contains a specific partial text. How can I count/sum that measure?

Ex:
The table looks something like this:

And the measure looks for "Direct - " using the following formula:

Count Direct Gross Margin Impact =

CONTAINSSTRING(SELECTEDVALUE('Division Quality Project Tracker'[Gross Margin Impact]), "Direct - ")

What I can't seem to figure out is how to get a sum of all the "True" outputs. IN this case - I would want the Total Count = 3

[v-stephen-msft · Accepted solution]

Hi @DemingPDCA ,

You could modify the measure as follows.

Count Direct Gross Margin Impact =
COUNTROWS (
    FILTER (
        'Division Quality Project Tracker',
        CONTAINSSTRING (
            'Division Quality Project Tracker'[Gross Margin Impact],
            "Direct - "
        )
    )
) + 0

You can see that it has a total of 3, and if you want each row to return a total, you can add ALLSELECTED function.

Count Direct Gross Margin Impact =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Division Quality Project Tracker' ),
        CONTAINSSTRING (
            'Division Quality Project Tracker'[Gross Margin Impact],
            "Direct - "
        )
    )
) 

Best Regards,

Stephen Tao

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

[Mikelytics]

HI @DemingPDCA 

Please try the following:

COunt =

SUMX(
   VALUES(Table[Column in your Fact table for granularity definition]),
   var __check = CONTAINSSTRING(SELECTEDVALUE('Division Quality Project Tracker'[Gross Margin Impact]), "Direct - ")
   Return
   IF(__check = TRUE(),1,0)
)

Based on your request its hard to say what to put into the VALUES() function. In the end my formula iterates through a list of items which and executes tzhe check. if it is true the the result is 1 else it is 0. In the end all 1 are summed up.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.