sum values with condition measure based on a dimension

[#1]

Product_Type	Sales	Expected result : Product Type without sales
Type1	899	0
Type2	345	0
Type3	0	1
Type4	567	0
Type5	235	0
Type6	456	0
Type7	0	1
Type8	46	0
Type9	0	1

I have a table like above one (agg all the rows based on sales) I am trying to create a measure like Product Type without sales

Used the expression =Count({$<Sales={"0"}>} distinct [Product_Type])

But it is not working ... I am getting '1' in all rows for the measure "Product Type without sales"

Just FYI, the actual table will have rows at day level . I have just showed you the aggregated table. So here sales is sum(sales) so that if I pull the product_type dimension it do a group by

can anybody help me to solve this??

Thanks

[#2 · Accepted solution]

Hi Jithu,

I think that you should use something like sum(If(AGGR(Sum(Sales), Product_Type)>0,1,0)). If you use this on a KPI object you should have the right value at least at Product Type level.

Regards,

Guillermo.