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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

12 REPLIES 12
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]``````

Helper I

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 Measurecount 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

Super User

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

Helper I

Hi,

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

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)

Solution Sage

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)
Helper I

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

Super User

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]``````

Super User

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? 🙂

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

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Helper I

HI,

Can you please provide measures calculation as well?

Thank you

Helper I

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

Solution Sage

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 Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors