Count values inside and outside objective

[flavio29]

Hello, i have the following data:

Date	Code	Values	Avg values based on each code	Count distinct codes	Count codes out of objective (<33)	Count codes inside objective (>=33)
1/1/2021	111156	31.0	32.1	1	1
1/1/2021	111156	33.2				1
2/1/2021	111157	34.1	33.6	1		1
2/1/2021	111157	32.0			1
2/1/2021	111157	34.6
3/1/2021	111189	35.0	35	1		1
Total (wrong)				3	2	3
Total (right)				3	1	2

i want to calculate the count of codes that are inside and outside a certain objective. i have the following measures:

count distinct codes = CALCULATE(DISTINCTCOUNT('Table'[Code]),FILTER('Table','Table'[Value]))

count inside objective = CALCULATE(DISTINCTCOUNT('Table'[Code]),FILTER('Table','Table'[Value]),'Table'[Value]>=33,'Table'[Value]<>blank())

count outside objective = CALCULATE(DISTINCTCOUNT('Table'[Code]),FILTER('Table','Table'[Value]),'Table'[Value]<33,'Table'[Value]<>blank())

The count of codes inside and outside of objective not always show the right value. I wanted the measures to be based on the average values for each code but until now it only gets worse the result. 

In addition i wanted to count the average values too but can't find the right measure.

The results are to be shown in a table with the dates concatenated by months.

Thanks for the attention.

[MFelix · Accepted solution]

Hi @flavio29 

You need to create the following measures:

Average_values_per_item =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Table'[Code], 'Table'[Values] ),
        'Table'[Code] = SELECTEDVALUE ( 'Table'[Code] )
    ),
    'Table'[Values]
)

DistinctCount = DISTINCTCOUNT('Table'[Code])

below =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Code] ),
    FILTER ( ALLSELECTED ( 'Table'[Values] ), [Average_values_per_item] < 33 )
)

above =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Code] ),
    FILTER ( ALLSELECTED ( 'Table'[Values] ), [Average_values_per_item] >= 33 )
)

Has you can see below and in attach PBIX file result are correct.

[flavio29]

Thank you Miguel, it really helped a lot!

I forgot to add that sometimes all the values are blank to some codes, so i dont want to count the codes in that situations. For that i stayed with my distinctcount measure and added one more filter to the below measure, like this:

Average_values_per_item =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Table'[Code], 'Table'[Values] ),
        'Table'[Code] = SELECTEDVALUE ( 'Table'[Code] )
    ),
    'Table'[Values]
)

DistinctCount = CALCULATE(DISTINCTCOUNT('Table'[Code]),FILTER('Table','Table'[Value]))

below =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Code] ),
    FILTER ( ALLSELECTED ( 'Table'[Code] ), [Average_values_per_item] < 33 )
    FILTER ( ALLSELECTED ( 'Table'[Code] ), [Average_values_per_item] <> 0 )
)

above =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Code] ),
    FILTER ( ALLSELECTED ( 'Table'[Code] ), [Average_values_per_item] >= 33 )

In below and above measures i also changed the Allselected argument to the Code column because was not giving the right results, but now works like a charm!

[MFelix]

Hi @flavio29 ,

Just one thing in the below measure you do not need to repeat the filter statment you can rewrite has:

below =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Code] ),
    FILTER ( ALLSELECTED ( 'Table'[Code] ), [Average_values_per_item] < 33  && [Average_values_per_item] <> 0 )
)