topic Count Indicator Achievement [DistinctCount, Summarize, Countrows] in DAX Commands and Tips

Count Indicator Achievement [DistinctCount, Summarize, Countrows]

Anonymous — Tue, 30 Aug 2022 02:11:43 GMT

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:

IndicatorName	Month	Target	Actual
Indicator1	01/01/2021	0	5
Indicator2	01/01/2021	5	1
Indicator3	01/01/2021	30	20
Indicator4	01/01/2021	2	0
Indicator5	01/01/2021	0	0
Indicator1	01/02/2021	5	0
Indicator2	01/02/2021	2	1
Indicator3	01/02/2021	0	8
Indicator4	01/02/2021	0	0
Indicator5	01/02/2021	0	0
Indicator1	01/03/2021	2	7
Indicator2	01/03/2021	0	2
Indicator3	01/03/2021	10	17
Indicator4	01/03/2021	8	4
Indicator5	01/03/2021	0	0

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

Indicator	Target	Actual	Achievement
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.

Re: Count Indicator Achievement [DistinctCount, Summarize, Countrows]

Jihwan_Kim — Tue, 30 Aug 2022 03:34:13 GMT

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Re: Count Indicator Achievement [DistinctCount, Summarize, Countrows]

Anonymous — Tue, 30 Aug 2022 05:53:23 GMT

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,

DIVIDE ( [Actual measure:], [Target measure:] )

)

Thanks again for your helpful hands.

Re: Count Indicator Achievement [DistinctCount, Summarize, Countrows]

Anonymous — Tue, 30 Aug 2022 06:53:29 GMT

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