Count products where average value is not zero

[BartVA]

Hello,

I have a table with (among others) the fields Date, Product, Stock.

I also have a Master Calendar table with Date, YearWeek, YearMonth, and so on.

I need a chart that shows:

• X-axis: YearWeek (with alternatives YearMonth, etc.)
• Y-axis: in plain English "the number of distinct products where the average Stock for the given period in the chart (eg. YearWeek or YearMonth) is different from 0, divided by the total number of products"

How do I make the Master Measure for this? Many thanks in advance!

I've been messing around with aggr but can't seem to get it right...

[rubenmarin · Accepted solution]

Hi, maybe with: Count(Distinct Aggr(If(Avg(Stock)<>0, Product), YearWeek,Product))/Count(distinct Product)

[BartVA]

Yes, great! Thank you!