How to count no. of values calculated by a measure >

[Anonymous]

Hi Everyoen, 

I have a measure which calculate On time Delivery % of each supplier I use a table from our ERP system which mark each line as late in the status column if it passes the due delivery date 

Lots Total = CALCULATE(count(OTD[Recipt Date]))

Late lots = CALCULATE(COUNTROWS(OTD),OTD[Status]="late")

OTD% = CALCULATE(DIVIDE([Lots Total]-[Late lots],[Lots Total])

I display those results per supplier each month using a column chart

i need to calculate the number of suppliers who have score above of 90% 

and the number of suppliers between 90% and 80%

is it possible to have these count from the values 

[manikumar34]

@Anonymous 

Try something like this.

CALCULATE(DISTINCTCOUNT(Table[Suppliers]),[OTD%]>0.9)

CALCULATE(DISTINCTCOUNT(Table[Suppliers]),[OTD%]>0.9 && [OTD%]<0.8)

Keep OTD% data type in decimals if the above is not working with percentage.

Regards, 

Manikumar

[Anonymous]

When I tried this formula I got an error 

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

any thoughts

[manikumar34 · Accepted solution]

@Anonymous , 

You need to use FILTER function with CALCULATE

CALCULATE(DISTINCTCOUNT(Table[Suppliers]),FILTER(TABLE,[OTD%]>0.9))

CALCULATE(DISTINCTCOUNT(Table[Suppliers]),FILTER(TABLE,[OTD%]>0.9 && [OTD%]<0.8))

Regards, 

Manikumar

[Anonymous]

Thank you very much this actualy worked for >90 and between 90 and 85 

but When I try to make one more measure for values less than 0.85 I got higher values than normal as if he counts the whole list 

here's my formula 

<0.85 = CALCULATE(DISTINCTCOUNT('60 supplier Master List 2020'[Supplier]),FILTER('60 supplier Master List 2020',[OTD%]< 0.85))

[manikumar34]

Formula is fine. That should work. 

Can you reverify the count again and check. 

Regards,

Manikumar

[manikumar34]

@Anonymous , 

If your problem solved, can you Mark this answer as a Solution to help others. 

Regards, 

Manikumar

[amitchandak]

@Anonymous , one way is to create measures like

> 90 = countx(filter(summarize(Table, Table[supplier], "_1",[OTD%]),[_1]>.9),[supplier])
>80 < 90 = countx(filter(summarize(Table, Table[supplier], "_1",[OTD%]),[_1]>.8 && [_1]<=.9),[supplier])

or create a Dynamic segmentation using an independent table on [OTD%]. Refer my video

https://www.youtube.com/watch?v=CuczXPj0N-k