Dividing by Dimension with filters

[Anonymous]

Hello, I have the database below and I need to know the Avg of total sales per salesman and sum the avgs in the total line.

The measure below is the measure with the filters I need to be kept

#Invoice = 

CALCULATE(

             DISTINCTCOUNT('table1'[invoiceno]),

             'table1'[ItemStatus] = "Paid" ||

             'table1'[ItemStatus] = "Unpaid" ||

             'table1'[ItemStatus] = BLANK() )

Then I need to calculate the Avg Total Sales for each Salesman based on the  [#Invoice] calculation above (distinct count of invoices with filters)

I tried to divide the #Invoice by the avg sales but it didn't work because in the  [ #Invoice ] I have filters.

How do I get the Average of total sales based on the calculation #Invoice?

Then I will need to aggregate these values by salesman

Salesman	InvoiceNo	InvoiceStatus	TotalSales
Kieran Kearns	52750	Paid	59.09
Kieran Kearns	190492	Paid	62.94
Kieran Kearns	150310	Unpaid	62.94
Kieran Kearns	125692	Paid	62.94
Kieran Kearns	131323	Paid	62.94
Kieran Kearns	180406		62.94
Kieran Kearns	152700	Waiting	62.94
Kieran Kearns	144922	Overdue	62.94
Kieran Kearns	143436	Overdue	62.94
Kieran Kearns	142426	Overdue	62.94
Kieran Kearns	136590	Paid	62.94
Kieran Kearns	176288	Paid	62.94
Kieran Kearns	113310	Paid	62.94
Kieran Kearns	100702	Overdue	62.94
Kieran Kearns	148045	Overdue	62.94
Kieran Kearns	145719	Unpaid	62.94
David Logan	145724	Paid	62.94
David Logan	137286	Paid	62.94
David Logan	137170	Paid	62.94
David Logan	200306	Unpaid	62.94
David Logan	195156	Unpaid	62.94
David Logan	188609	Unpaid	62.94
David Logan	99696		62.94

Desired results

For Kieran Kearns 

[#Invoice] = 10 invoices 

[Total Sales] = 625.55

[Avg Sales p salesman] = 62.55 (625.55 / 10)

For David Logan 

[#Invoice] = 7 invoices 

[Total Sales] = 440.58

[Avg Sales p salesman] = 62.94 (440.58 / 7)

Then the total I need the sum of the averages of all salesman instead avg.

Any way I can achieve this? In the real dataset I will have to do this for a huge set of salesman

Thanks!

[HarishKM]

@Anonymous  Hey 
I have created 3 measures
Total sales = sum (sales[totalsales])
# of invoices = countrows(sales)
avg sales = AVERAGE(Sales[TotalSales])
go to filter or slicer and select Paid ,unpaid,Blank to fulfill your requirement.
Left side matrix is filtered by slicer and right side matrix is hard coded filter .

[amitchandak · Accepted solution]

@Anonymous , check the measures

#Invoice =
CALCULATE(
DISTINCTCOUNT('table1'[invoiceno]),
Filter('table1', 'table1'[ItemStatus] in( "Paid", "Unpaid") || isblank('table1'[ItemStatus]) ))


#Avg Invoice =
divide(sum('table1'[TotalSales]),
CALCULATE(
DISTINCTCOUNT('table1'[invoiceno]),
Filter('table1', 'table1'[ItemStatus] in( "Paid", "Unpaid") || isblank('table1'[ItemStatus]) )))

#Avg Invoice by sales man = calculate([#Avg Invoice], filter(allselected('table1'), 'table1'[Salesman] = max('table1'[Salesman] )))