Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
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 |
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
@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.
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
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
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).
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]
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/
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 |
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 |
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |