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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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