Count number of one's and zero's for each product

[pry76092]

Hi,

I have a table which contains colums: date, store_id, product_id, transaction_count and sales. if the transaction_count is greater than 0 then sales is 1 else its 0. So, now I want a measure which can help me to find number of one's and zero's for each product sold in each store.

Also, I want to know is it possible to get count of zero's as 1 if there is no sales mentioned for a particular date. So, in the below table we can see count of zero is 2 for store 21 and product no 42A5 because it has no sales on 12-05-2021 and 14-05-2021(but it has no record).

Storeid	product_no	date	sale	transaction_count	Measure count of zero	Measure  count of one's
21	42A5	11-05-2021	1	3	2	3
22	42A5	11-05-2021	1	7	0	1
21	42A5	12-05-2021	0	0	2	3
23	42A5	11-05-2021	0	0	1	0
23	42A6	11-05-2021	1	5	0	2
23	42A6	12-05-2021	1	6	0	2
21	42A5	13-05-2021	1	2	1	2
21	42A5	15-05-2021	1	5	2	3

Thank you

[amitchandak]

@pry76092 , with help from a date table, joined to sales

M1 = sum(Table[sales]) +0

measure for sale =0 and > 0

countx(filter(summarize(Table, Table[Store], 'date'[Date], "_1", [M1]), [_1] =0 ), [Store])

countx(filter(summarize(Table, Table[Store], 'date'[Date], "_1", [M1]), [_1] >0 ), [Store])

[pry76092]

Hi,

Thanks for your solution but its not working as per requirement.

Its simplying puting 1's wherever the sale is 0 and 1 but its not adding them up to tell count of zero's and one's.

So, i tried other calculation which is returning correct information for count of one's but for count of zero's its not condsidering the date when there is no record of sales. example below

The meaure which I'm using is:

count of zero's= 

var _product=SELECTEDVALUE(TABLE[product no])

var _store=SELECTEDVALUE(TABLE[store_id])

return

CALCULATE(COUNTROWS(TABLE),ALL(TABLE),TABLE[sales]=0,TABLE(store_id)=_store,TABLE[product no]= _product)+0

count of one's= 

var _product=SELECTEDVALUE(TABLE[product no])

var _store=SELECTEDVALUE(TABLE[store_id])

return

CALCULATE(COUNTROWS(TABLE),ALL(TABLE),TABLE[sales]=1,TABLE(store_id)=_store,TABLE[product no]= _product)+0

[Anonymous · Accepted solution]

HI @pry76092,

Did these zero records really exist in your tables? If they include in your table, you can try to use the following measure formula to calculate the two counts based on store and product:

zero count =
CALCULATE (
    COUNTROWS ( VALUE ( Table[date] ) ),
    FILTER ( ALLSELECTED ( Table ), Table[sales] = 0 ),
    VALUES ( Table[store_id] ),
    VALUES ( Table[product no] )
)

Not zero count =
CALCULATE (
    COUNTROWS ( VALUE ( Table[date] ) ),
    FILTER ( ALLSELECTED ( Table ), Table[sales] <> 0 ),
    VALUES ( Table[store_id] ),
    VALUES ( Table[product no] )
)

If not, it means you need to calculate the missed records based on current records instead count rows with filters to calculate zero records.

For this scenario, can you please share some dummy data that keep the raw data structure? They will help us clarify your data structure to test the coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng