topic Re: Cumulative unique count based on sales values-DAX in DAX Commands and Tips

Cumulative unique count based on sales values-DAX

Anonymous — Mon, 15 Nov 2021 06:12:59 GMT

Hi expert,

I am stucking on to calculate cumulative unique count in DAX month on month basis some criteria,

please help me to get this done.

Thanks a ton in advance.

Icey Tanushree_Kapse VahidDM Anonymous amitchandak pbig administrator PBICommunity PBCommunity

Raw data and summary below.

Please help me on this.

Thanks

Re: Cumulative unique count based on sales values-DAX

VahidDM — Mon, 15 Nov 2021 06:23:03 GMT

Hi Anonymous

Try this measure:

Measure1 = VAR _A = CALCULATE ( DISTINCTCOUNT ( 'Table'[Cust] ), FILTER ( ALL ( 'Table' ), 'Table'[Months] < MAX ( 'Table'[Months] ) && 'Table'[sales flag] = 1 ) ) RETURN IF ( ISBLANK ( _A ), 0, _A )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Cumulative unique count based on sales values-DAX

Anonymous — Mon, 15 Nov 2021 08:04:31 GMT

Thank u so much for quick reply and looking correct . but while applying in my live data , not getting correct output

Sorry My data is at invoice level hence one customer can have mulitple records basis on their billing in same month but have to check the sales at customer level while counting in month.

like base raw data will be.

output will be same

Re: Cumulative unique count based on sales values-DAX

Anonymous — Wed, 17 Nov 2021 09:49:51 GMT

Thanks VahidDM.

Basis on your logic , I have added on other criteria basis my data and have applied in my project is working . Got what I was looking for.

Dist_Count_Retailer_for_repeat_Analysis = calculate(DISTINCTCOUNT('db_CSA_Data Yield_Sec_Test'[Distributor+finalvittrackcode]),FILTER(SUMMARIZE('db_CSA_Data Yield_Sec_Test','db_CSA_Data Yield_Sec_Test'[Distributor+finalvittrackcode],Dates[CalYear],Dates[MOC_Name_Auto_New],'db_CSA_Data Yield_Sec_Test'[Pri_Sec],'db_CSA_Data Yield_Sec_Test'[DistributorCode],SAP_CSA_Tbl_Customer_Master[DB Name],SAP_CSA_Tbl_Customer_Master[State_Mapping.Region-Group],SAP_CSA_Tbl_Customer_Master[State_Mapping.State Group],SAP_CSA_Tbl_Customer_Master[City name],'Channel_Index Final'[Final Channel],Dates[MOC_Code_Auto_New for repeat],"Sum of Qty",SUM('db_CSA_Data Yield_Sec_Test'[Quantity]),"Sum of Yield",SUM('db_CSA_Data Yield_Sec_Test'[Actualyield])),[Sum of Qty]<>0&&'db_CSA_Data Yield_Sec_Test'[Pri_Sec]="Sec inc P=S"))

Thanks.

Re: Cumulative unique count based on sales values-DAX

Anonymous — Fri, 19 Nov 2021 13:25:19 GMT

Thanks VahidDM.

Basis on your logic , I have added on other criteria basis my data and have applied in my project is working . Got what I was looking for.

Thanks.