Forum Discussion

Yarafat's avatar
Yarafat
Frequent Visitor
7 years ago

doubly value from distinct count with Positive Value

Hi Team,

 

I need your help to get the Distinc count formula. i have data of Secondary sales . I need to see the product wise distinc Outlet count who have a positive sales. In our business Day 1 we have create a invoice for outlet and day 2 we are going to deliver the product in outlet , But some outlet the Returing the Product which have order by Day 1. That means we create positive Sales at Day 1 and Negetive sales at Day 2. An example : 

Sample Data
DateOutlet CodeProduct codeQty
9/20/2018CT0013035
9/21/2018CT001303-3
9/20/2018CT0023037
9/21/2018CT002303-7
9/20/2018CT0063039
9/21/2018CT006303-5
9/20/2018CT0033034
9/20/2018CT0033037
9/20/2018CT0043037
9/20/2018CT0014045
9/21/2018CT001404-3
9/20/2018CT0024047
9/21/2018CT002404-7
9/20/2018CT0024049
9/21/2018CT002404-5
9/20/2018CT0034044
9/20/2018CT0084049
9/20/2018CT008404-9
9/20/2018CT011404-9
9/20/2018CT0034047
9/20/2018CT0044047

 

Pivot table for Getting Positive sales outlet in sheet. 

Pivot table for getting Positive sales outlet
Product CodeOutlet CodeSum of Qty
303CT0012
 CT0020
 CT00311
 CT0047
 CT0064
404CT0012
 CT0024
 CT00311
 CT0047
 CT0080
 CT011-9
Grand Total 39

 

Final Expectec resutl :

final Result
Product CodeDistinc Outlet count
3034
4044

 

 

Pls. find the attached link for sample file. drive Link : "https://drive.google.com/file/d/1WsgQrXXNZnqK8GgKlPGaCtuID3XPfTFM/view?usp=sharing" 

 

 

Regards 

Yeasin 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    The expected result of 4 for each, that is not counting the negative and 0's in your second table, correct?

     

    In general, you would do something like:

     

    Measure = 
VAR _code = MAX([Product Code])
RETURN
COUNTROWS(FILTER(SUMMARIZE('Table',[Product Code],[Outlet Code],"__Qty",SUM([Qty])),[Product Code] = __code))