Get count of invoice based on condition

Junarkar

Member
Hi,

I'm trying to create a report based on few conditions. I have excel file with 3 sheets - Promo Master, Sales Data and Report. What I want is the count of invoices in which the staff sold a particular product which is in promo master.

Example;
Promo Master;
 SKU 221022​ 220808​ 218882​

Sales data;
 Billing Date Sales Person ID Invoice Article No 01-03-2020​ 115000​ SLA123456000 205143​ 01-03-2020​ 115000​ SLA123456001 219133​ 01-03-2020​ 16123​ SLA123456002 221022​ 01-03-2020​ 16123​ SLA123456002 224029​ 01-03-2020​ 137778​ SLA123456004 219836​ 01-03-2020​ 16123​ SLA123456005 168541​ 01-03-2020​ 16123​ SLA123456006 224029​ 01-03-2020​ 16123​ SLA123456007 200898​ 01-03-2020​ SLA123456008 221022​

Report Format;
 Date Emp Code Emp Name Count of Total No. of Inv. Count of Promo sale inv. 01-03-2020​ 16123​ Ram 4​ 2​ 02-03-2020​ 03-03-2020​ 04-03-2020​ 05-03-2020​

In this you can see that employee 16123 has made couple of invoices out of which two invoices contain article code which is present in Promo master (221022).
Also note that one invoice can have multiple articles (invoice no. SLA123456002 in this example which have two articles in it). This should be counted as one.

Sample file is attached.

Regards
Junarkar

Attachments

• 13.7 KB Views: 6

Peter Bartholomew

Well-Known Member
Firstly, I would convert all tabular data to Excel Tables. That is a personal preference that allows me to understand formulas.
Given that, I suggest you need a helper column on the sales table to indicate whether the item is on promotion or not.
= COUNTIFS( Promo[SKU], [@[Article No]] )
This avoids the need for repeated searches of the Promo table,

I do not agree with your counts but the formula could be
= COUNTIFS( Sales[Sales Person ID], [@[Emp Code]], Sales[Promo], ">0", Sales[Billing Date], [@Date] )

Attachments

• 18.8 KB Views: 5

p45cal

Well-Known Member
As with Peter's response I've added a column to your Sales Data table and made it into an Excel table.
I've also added a Sales Person ID in cell D10 and populated some names in Sales Person Name column.
Bearing the above in mind, is the table at A10 of the Report sheet showing the correct data?

Attachments

• 300.3 KB Views: 5

Junarkar

Member
Firstly, I would convert all tabular data to Excel Tables. That is a personal preference that allows me to understand formulas.
Given that, I suggest you need a helper column on the sales table to indicate whether the item is on promotion or not.
= COUNTIFS( Promo[SKU], [@[Article No]] )
This avoids the need for repeated searches of the Promo table,

I do not agree with your counts but the formula could be
= COUNTIFS( Sales[Sales Person ID], [@[Emp Code]], Sales[Promo], ">0", Sales[Billing Date], [@Date] )
First sorry for the late replay. and Thanks Peters, It worked....

Junarkar

Member
As with Peter's response I've added a column to your Sales Data table and made it into an Excel table.
I've also added a Sales Person ID in cell D10 and populated some names in Sales Person Name column.
Bearing the above in mind, is the table at A10 of the Report sheet showing the correct data?
Sorry the delay in replay.. Yes that worked.... Thanks alot for the solution.