Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pry76092
Helper I
Helper I

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

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
2142A511-05-2021   13         1          2
2242A511-05-2021   17         0          1
2142A512-05-2021   00         1          2
2342A511-05-2021   00         1          0
2342A611-05-2021   15         0          2
23 42A612-05-2021   16         0          2
2142A513-05-2021   12         1           2

 

 

 

Thanks in advance

12 REPLIES 12
tamerj1
Super User
Super User

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]

 

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

2142A511-05-2021   13         2          3
2242A511-05-2021   17         0          1
2142A512-05-2021   00         2          3
2342A511-05-2021   00         1          0
2342A611-05-2021   15         0          2
23 42A612-05-2021   16         0          2
2142A513-05-2021   12         1           2
2142A515-05-2021   15         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

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

Hi,

 

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

 

 

Whitewater100
Solution Sage
Solution Sage

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_0-1649277046578.png

 

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)

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

2142A511-05-2021   13         2          3
2242A511-05-2021   17         0          1
2142A512-05-2021   00         2          3
2342A511-05-2021   00         1          0
2342A611-05-2021   15         0          2
23 42A612-05-2021   16         0          2
2142A513-05-2021   12         1           2
2142A515-05-2021   15         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
Super User
Super User

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
Super User
Super User

Hi @pry76092 ,

 

Here a solution:

tomfox_0-1649275956123.png

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

HI,

 

Can you please provide measures calculation as well?

 

 

Thank you

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
2142A511-05-2021   13         2          3
2242A511-05-2021   17         0          1
2142A512-05-2021   00         2          3
2342A511-05-2021   00         1          0
2342A611-05-2021   15         0          2
23 42A612-05-2021   16         0          2
2142A513-05-2021   12         1           2
2142A515-05-2021   15         2           3

 

 

Thank you

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors