Count number of one's and zero's for each product sold in each store

[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.

Storeid	product_no	date	sale	transaction_count	count of zero	count of one's
21	42A5	11-05-2021	1	3	1	2
22	42A5	11-05-2021	1	7	0	1
21	42A5	12-05-2021	0	0	1	2
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

Thanks in advance

[tamerj1]

Hi @pry76092 
Here is a sample file with the solution https://www.dropbox.com/t/PzTnWNh8YEDlU3i2

The solution is based on measures (Not calculated columns):

count of One's = 
SUMX (
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Storeid], 'Table'[product_no] ) ),
    'Table'[sale]
)

count of zoro's = 
VAR ProductStoreTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Storeid], 'Table'[product_no] ) )
VAR LastSalesDate = 
    MAXX ( ProductStoreTable, 'Table'[date] )
VAR FirstSalesDate =
    MINX ( ProductStoreTable, 'Table'[date] )
RETURN
    DATEDIFF ( FirstSalesDate, LastSalesDate, DAY ) + 1 - [count of One's]

[pry76092]

Hi,

Thanks for your solution but this measure is not working for me and I dont understand why.

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

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

 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).

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

[tamerj1]

@pry76092 
can you please explain further what is the issue? Please provide screenshots if possible

[pry76092]

Hi,

Actually, its working on the sample data which I have provided but I dont understand why its not working on real dataset.

[Whitewater100]

Hello:

Please find attached pbix with results. I added tables so you will have a model to work with.https://drive.google.com/file/d/1d73N14EmEKN47Ro3mchntCVrW3k_T2Aa/view?usp=sharing 

Active Sale =

CALCULATE(

COUNTROWS(Data),

Data[Sale] >0)

No Sale =

CALCULATE(

COUNTROWS(Data),

Data[Sale] =0)

[Whitewater100]

Hi:

Lot's of great replies for the calc columns. You had asked for measures,are the ones sent yesterday what you need? A data model iwas provided to allow for proper future analysis. Thelatest request is hard to follow, for me.. File from yesterday link below.

https://drive.google.com/file/d/1d73N14EmEKN47Ro3mchntCVrW3k_T2Aa/view?usp=sharing

Active Sale(1's) =

CALCULATE(

COUNTROWS(Data),

Data[Sale] >0)

No Sale(0's) =

CALCULATE(

COUNTROWS(Data),

Data[Sale] =0)

[pry76092]

Hi,

Thanks for your solution but my requirement is different, sorry if I'm not able to explain my question to you. I'll try to be more elaborate.

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

 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).

[tamerj1]

Hi @pry76092 

you can try

count of One's =
SUMX (
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Storeid], Table[product_no] ) ),
    [sale]
)

count of zoro's =
COUNTROWS (
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Storeid], Table[product_no] ) )
) - Table[count of One's]

[tackytechtom]

Hi @pry76092 ,

Here a solution:

And here the calculated columns (I did not create a measure since according to your table it looked more like calculated columns that you were heading for. Let me know if you wanna have real measures):

Count of Zero = 
VAR _product = Table[product_no]
VAR _store = Table[Storeid]
RETURN
CALCULATE ( 
    COUNTROWS ( Table ),
    ALL ( Table ),
    Table[sale] = 0,
    Table[Storeid] = _store,
    Table[product_no] = _product
)

Count of Ones = 
VAR _product = Table[product_no]
VAR _store = Table[Storeid]
RETURN
CALCULATE ( 
    COUNTROWS ( Table ),
    ALL ( Table ),
    Table[sale] = 1,
    Table[Storeid] = _store,
    Table[product_no] = _product
)

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[pry76092]

HI,

Can you please provide measures calculation as well?

Thank you

[pry76092]

Hi,

Thanks for your solution. It worked for me but 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	count of zero	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

[daXtreme]

I'll give you a hint. You've got 2 choices if you want to stay sane with your calculations and make them correct. Either record all days for all stores and all products (even if there were no sales) or... create a good dimensional model with separate dimensions and facts. If you start mixing these two methods, you'll have a very hard time trying to write fast and correct formulas. This willl be extremely hard or close to impossible.