DAX Measure to count items where combined sum >= 10

[g_m_j]

Hi all,

Been battling this one for too long so I'm seeking help here.

I have data made up of Area based Activity requirements.

There are 7 different areas.

If an Area completes an Activity 10 times (either in one go, or across multiple attempts), then they are good (i.e. green on my RAG).

I then need to count the "good" areas.

Example Areas:

Area_Name

Area_One

Area_Two

Area_Three

Area_Four

Area_Five

Area_Six

Area_Seven

Here is sample data:

Activity	Area_Name	Total
Care	Area_One	10
Care	Area_Two	6
Care	Area_Four	10
Hygiene	Area_One	10
Hygiene	Area_Two	9
Hygiene	Area_Two	2
Hygiene	Area_Two	3
Hygiene	Area_Two	10
Hygiene	Area_Three	1
Listening Activity	Area_One	10
Listening Activity	Area_Three	2

Here's the DAX I currently have, but unfortunately it just seems to count the distinct Area_Names for all (whereas I only want to count them if they have >= 10)

Distinct_Area_Count_10x = CALCULATE(DISTINCTCOUNT(Report_Summary[Area_Name]),
FILTER(ALL('Activity_Breakdown'[Total]),
SUM('Activity_Breakdown'[Total])>=10))+0

Here is the output I am getting from the above, and the desired outcome I would like to achieve:

	Current Output	Desired Output
Activity	Sep-21	Sep-21
Care Activity	3	2
Hygiene Activity	4	2
Attention Activity	0	0
Listening Activity	2	1

Thanks in advance 🙂

[VahidDM · Accepted solution]

Hi @g_m_j 

Try this measure:

Distinct_Area_Count_10x =
VAR _A =
    FILTER (
        SUMMARIZE (
            Activity_Breakdown,
            Activity_Breakdown[Activity],
            Report_Summary[Area_Name],
            "Total", SUM ( Activity_Breakdown[Total] )
        ),
        [Total] >= 10
    )
RETURN
    COUNTROWS ( _A )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[g_m_j]

This works perfectly, thank you! 

I had a feeling SUMMARIZE might be involved somehow, but haven't used it before - so is great to see it in action. Thanks again.

[smpa01]

@g_m_j  can you try this measure

Measure =
CALCULATE (
    DISTINCTCOUNT ( Activity_Breakdown[Area_Name] ),
    CALCULATETABLE (
        Activity_Breakdown,
        FILTER (
            Activity_Breakdown,
            CALCULATE (
                SUM ( Activity_Breakdown[Total] ),
                ALLEXCEPT (
                    Activity_Breakdown,
                    Activity_Breakdown[Activity],
                    Activity_Breakdown[Area_Name]
                )
            ) >= 10
        )
    )
)

[g_m_j]

That didn't quite do it unfortunately. Only a slight change in the measure results (from 3, 4, 2 to 3, 4, 1).

However, I can see from your results that your version works as expected.

[smpa01]

@g_m_j  I acted on the sample you gave and it clearly worked there as evident in the attached pbix. If you have a difreent sample, then revise your question.