Forum Discussion

flavio29's avatar
flavio29
Frequent Visitor
5 years ago
Solved

Count values inside and outside objective

Hello, i have the following data:

DateCodeValuesAvg values based on each codeCount distinct codesCount codes out of objective (<33)

Count codes inside objective (>=33)

1/1/202111115631.032.111 
1/1/202111115633.2   1
2/1/202111115734.133.61 1
2/1/202111115732.0  1 
2/1/202111115734.6    

3/1/2021

111189

35.0351 1

Total (wrong)

   32 3

Total (right)

   312

 

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.
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

     

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      flavio29
      Frequent Visitor
      5 years ago

      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's avatar
        MFelix
        Super User
        5 years ago

        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 )
)