Forum Discussion
finding values not zero
Greetings,
This simple DAX formula is eluding me.
I have a report filtered by Supplier, and I wish to count the number of customers who have purchased some of their product in 2017 (Gross > 0 )
I wish to put up widget to show the # of customers in 2017, of course this value would change for each Supplier sliced.
(Slicing and filtering by Year 2017, user_id, manufacture_id)
As always the help of the community is always appreciated.
Best from Nova Scotia's beautiful Annpolis Valley.
9 Replies
Hi,
Try this
=CALCULATE(DISTINCTCOUNT(Data[id]),Data[Gross]>0)
Select any year/Supplier via the Filter section/slicer.
I have assumed that id is the Customer id.
Not quite the solution to my issue, likely due to my poor explanation.
I have made some progress (I think).
ADF = COUNTAX(FILTER(gross_sales,[Sales 2017]>0),[Gross])
Sales 2017 = CALCULATE(SUM(gross_sales[Gross]),gross_sales[year]=2017)
along with manufacturer_id, and user_id, I have this sample table. In effect, I am looking to count the number of items in column ADF (the values are the actual number of transaction to comprise the Sales 2017 value).
manufacturer_id user_id Sales 2017 ADF 102 73 $0 102 74 $6,008 3 102 75 $0 102 76 $14,972 7 102 77 $4,934 10 102 78 $65,662 12 102 79 $0 102 80 $0 102 82 $0 102 83 $0 102 84 $1,325 4 102 85 $0 102 86 $9,487 10 The answer I am looking for in this sample, would be 6.
I very appreciate your time, with many thanks.
Hi,
=CALCULATE(COUNTA(Data[user_id]),Data[Sales 2017]>0)
Does this work?
