Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Indicator Achievement [DistinctCount, Summarize, Countrows]

Hi there, 

Newbie is here. I have been stuck for 1 month to fix my dashboard. Hope someone can help me.

I have a raw data like this:

IndicatorNameMonthTargetActual
Indicator101/01/202105
Indicator201/01/202151
Indicator301/01/20213020
Indicator401/01/202120
Indicator501/01/202100
Indicator101/02/202150
Indicator201/02/202121
Indicator301/02/202108
Indicator401/02/202100
Indicator501/02/202100
Indicator101/03/202127
Indicator201/03/202102
Indicator301/03/20211017
Indicator401/03/202184
Indicator501/03/202100

And I've managed to get this kind of table which is as expected - with slicer month active

IndicatorTargetActualAchievement
Indicator1[Sum(Target)][Sum(Actual)]divide (Sum(Actual), Sum(Target))
Indicator2"""
Indicator3"""
Indicator4"""
Indicator5"""

 

But when I want to use a Card that visualizes the "number of indicators that reach the target by at least 90%", I don't get what I expect. I thougt it was because I should use the summarize function. But when I use summarize function, month slicer didn't work.

 

What I am expecting is, when I Click the Month slicer for 'month 01' and 'month 02', the Card should showing: 

 

I have managed for VAR Indicator with Target:

Indicator with Target = CALCULATE(DISTINCTCOUNT(Query1[Indicator]),FILTER(Query1,Query1[Target]>0))
 
But for the Indicator Achieved Target, I still stuck to produce this variable.
SUM Achievement Indicator = Query1[SUM Actual]/Query1[SumTarget]
Indicator Achieved Target = CALCULATE(DISTINCTCOUNT(Query1[Indicator]),FILTER(query1,Query1[SUM Achievement Indicator]>0.89)) --> the result is not as expected.
 
Really appreciate your support on this.
Thank you in advance.
COUNTROWS
distinct count
SUMMARIZE
SUMMARIZECOLUMNS

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Jihwan_Kim 

      Thanks a lot for your effort. I just clone your DAX to my dataset. Unfortunately, for the 'Indicator Achieved' VAR, it's still counting non targeted indicator if it has 'Actual' value. This is not what I expecting before.

      from above, this supposed to be '7' Indicator Achieved not 9.

       

      Is it because of [HASONEVALUE 'Achievement Measure'] ? Is there any way to adjust from this:

      Achievement measure: =
      IF (
          HASONEVALUE ( Indicator[IndicatorName] ),
          IF (
              [Target measure:] = 0
                  && [Actual measure:] <> 0,
              1,
              DIVIDE ( [Actual measure:], [Target measure:] )
          )
      )
       
      Thanks again for your helpful hands.
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jihwan_Kim . I have managed the rest. Just to add conditional in countrows part and it works. 

     

    Indicator Achieved NEW = COUNTROWS ( FILTER ( Indicator, [Achievement measure] >= 0.9 && [SUM Target NEW]<>0) )

     

    You've saved my day! Thank you