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.
I'm trying to count the numbers of products that have no sales at a store. I'm trying to go through a virtual table route, not working. What would be the best way of going about this.
Count of Stores with Zero Product Sales =
COUNTROWS(FILTER(SUMMARIZECOLUMNS('Product'[Product_Description],Store_List[STORE NAME]),[Net Sales]=0))
I'm actually using another formula to visual the voids. All i want is to Count the Voids
Void = SWITCH(TRUE(),[Net Sales]>0,"Stock",[Net Sales]=0,"Void")
Any help would be much appreciated.
could you please provide the sample data and the expected output?
Proud to be a Super User!
This is a sample dataset and result. My solution didn't work when selection different products.
Hi,
Share the link from where i can download your PBI file.
I want to filter by Region/Week/Product and also use it in a Table. As you can see from the file it gives incorrect item voids counts when displayed in a table.
Attached is the link
https://drive.google.com/file/d/1PApr9ZmMPvGNP2kbY0LlfI0rKIIXD-yN/view?usp=sharing
Hi,
I cannot understand. Please let me know the exact result you want to see in the Table.
I think i figure it out, not exactly the best way since it doesn't work with certain filters.
Essentially it cross filters the store/product dimension and look for cells that have no data and then count the rows. I'm sure there many ways to do this. I was trying to do it using a virtual table via Calculatetable or SummarizedColumns,
Item Voids =
COUNTROWS(EXCEPT (
CROSSJOIN ( VALUES ( Store_List[STORE NAME] ), VALUES ( 'Product'[Product_Description]) ),
SELECTCOLUMNS (
'Daily Data',
"Customer", 'Daily Data'[Store],
"Product", 'Daily Data'[Product_Description]
)
))
@Tuan , Try like
Count of Stores with Zero Product Sales =
count(FILTER(SUMMARIZE('Product','Product'[Product_Description],Store_List[STORE NAME],"_1",[Net Sales]),[_1]=0))
Flag =
Maxx(SUMMARIZE('Product','Product'[Product_Description],Store_List[STORE NAME],"_1",[Net Sales]),if([Net Sales]>0,"Stock","Void"))
Doesn't work for me, says function only accepts a column reference.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |