Forum Discussion
Solved
Count values inside and outside objective
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 1...
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.
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.
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!