Forum Discussion

DemingPDCA's avatar
DemingPDCA
Helper II
3 years ago
Solved

Sum/Count Measure Output

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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.