Counting products above target

Question

I would like to calculate how many products are above or below target? i will put a simple representation of my data model. In my fact table i have the first 5 columns and I calculate 3M Average Sales and calculate the KPI by dividing stock by the average and then multiply by 30 (Stock * 30 / 3M Avg Sales), in this case the KPI is a measure not a calculated column I just added it below for illustration. I also have a target column for each row however I calculate another measure for the weighted average of the target as well based on the sales.

Date	ProductID	Customer	Stock	Sales	3M Avg Sales	KPI	Target
Jan-23	123	CustomerA	101	93			30
Feb-23	123	CustomerA	94	96			30
Mar-23	123	CustomerA	100	106	98.3	30.50847	30
Apr-23	123	CustomerA	108	100	100.7	32.18543	30
May-23	123	CustomerA	101	98	101.3	29.90132	30
Jun-23	123	CustomerA	95	92	96.7	29.48276	30
Jul-23	123	CustomerA	110	95	95.0	34.73684	30
Jan-23	456	CustomerA	108	96			30
Feb-23	456	CustomerA	104	100			30
Mar-23	456	CustomerA	92	91	95.7	28.85017	30
Apr-23	456	CustomerA	92	95	95.3	28.95105	30
May-23	456	CustomerA	93	91	92.3	30.21661	30
Jun-23	456	CustomerA	108	94	93.3	34.71429	30
Jul-23	456	CustomerA	97	95	93.3	31.17857	30

My product dimension is as follows:

ProductId	Product	ProductDescription
123	A	Bike
456	B	Scooter
789	C	Wheel

I have tried two methods but they dont seem to work as expected.

Products Above Target =

-- Used to show the count of products that are above target

IF(ISBLANK(

COUNTX (

FILTER ( ProductID, [KPI] > [Weighted Target] ),

Product[ProductID]

)), 0,

COUNTX (

FILTER ( ProductID, [KPI] > [Weighted Target] ),

Product[ProductID]

)

Products Above Target =

-- Used to show the count of products that are above target

CALCULATE (

DISTINCTCOUNT ( FACT[ProductID]),

FILTER ( FACT, [KPI] > [Weighted Target] )

)

Thank you!

amitchandak · Accepted Answer

Anonymous , Try like 
&nbsp;
Countx(FILTER ( Values( FACT[ProductID]),&nbsp;[KPI] &gt; [Weighted Target] ) , [ProductID])
&nbsp;
for avg better way is
&nbsp;
3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Forum Discussion

Counting products above target

1 Reply

Related content

Classification count by Product Range

Counting nulls

Count of products

Production Lessons

Stacked Product Count