Need help in calcualting the avg distinct values

[MichaelJackpbi]

Hi friends,
I have 3 tables including order, salesman and product like blow:

OrderCode	SalesManCode	Productcode	OrderDate	Quantity	Amount
102227	10	1	12/12/2016	12	240
102228	11	2	12/12/2016	22	343
102225	5	3	12/12/2016	14	223
102230	4	1	12/12/2016	21	221
102207	11	2	12/12/2016	44	332
102221	14	4	12/12/2016	22	453
102252	11	5	13/12/2016	11	256
102206	16	5	13/12/2016	13	431
102244	7	1	13/12/2016	56	316
102214	19	2	13/12/2016	61	395
102206	16	3	13/12/2016	25	293
102206	1	7	13/12/2016	14	413
102251	17	8	13/12/2016	56	267
102237	4	6	13/12/2016	30	333
102208	19	4	13/12/2016	47	277
102248	14	10	13/12/2016	26	374
102255	17	4	14/12/2016	39	316
102215	11	3	14/12/2016	29	296
102232	2	9	14/12/2016	31	355
102246	15	13	14/12/2016	45	274
102212	14	16	14/12/2016	16	340
102225	11	9	14/12/2016	52	278
102243	5	1	14/12/2016	52	440
102208	7	3	14/12/2016	60	305
102235	19	13	14/12/2016	54	412
102251	9	7	14/12/2016	59	320
102233	13	4	14/12/2016	30	240
102242	12	13	15/12/2016	46	251
102241	12	11	15/12/2016	58	407
102227	18	7	15/12/2016	29	400
102221	4	13	15/12/2016	62	308
102208	5	13	15/12/2016	51	344
102250	11	4	15/12/2016	23	239
102236	10	6	15/12/2016	12	230
102214	10	10	15/12/2016	33	328
102227	18	16	15/12/2016	26	274
102229	8	9	15/12/2016	52	358
102214	9	3	15/12/2016	45	292
102211	11	3	15/12/2016	41	302
102247	8	7	15/12/2016	20	232
102219	19	15	15/12/2016	62	236
102216	13	3	15/12/2016	30	262
102220	6	10	15/12/2016	52	237
102204	17	10	15/12/2016	61	325
102231	15	6	15/12/2016	60	284
102242	7	13	15/12/2016	12	295
102246	11	13	16/12/2016	46	375
102236	12	5	16/12/2016	50	330
102232	2	8	16/12/2016	19	430
102249	4	9	16/12/2016	19	317
102217	7	8	16/12/2016	39	258
102250	11	8	16/12/2016	50	250
102234	2	5	16/12/2016	13	357
102209	9	16	16/12/2016	49	421
102233	18	15	16/12/2016	23	367
102234	12	10	16/12/2016	55	283
102227	19	12	16/12/2016	40	241
102243	5	13	16/12/2016	27	324
102232	12	6	16/12/2016	17	272
102206	2	6	16/12/2016	47	378
102206	16	10	16/12/2016	27	351
102204	7	2	16/12/2016	25	412

SalesManCode	SalesManName	SalesType
1	Jack	Phone
2	Michael	Phone
3	Susan	Direct
4	Lina	Direct
5	John	Internet
6	Alice	Agent
7	Phillip	Agent
8	Mina	Internet
9	Tina	Internet
10	Fizan	Internet
11	Anna	Phone
12	Peter	Agent
13	Sheng	Agent
14	Shuhn	Agent
15	Son	Phone
16	Hellien	Internet
17	Antony	Phone
18	Han	Direct

ProductCode	ProductName	ProductLine	ProductCategory
1	A	L1	C1
2	B	L1	C1
3	C	L1	C1
4	D	L2	C1
5	E	L2	C1
6	F	L3	C2
7	G	L3	C2
8	H	L4	C2
9	I	L4	C2
10	K	L4	C2
11	L	L5	C3
12	M	L5	C3
13	N	L5	C3
14	O	L6	C3
15	P	L6	C3
16	Q	L6	C3
17	S	L7	C3

I need to create a report to calculate the avg of lineproduct per each SalesmanType daily! it means i have to avg how many lineproducts sold per each SalesmanType in daily. Could anyone give some advices?

Regards,
J.

[gooranga1]

Could you do it with either a matrix or a bar chart with a calculated measure?

[MichaelJackpbi]

Hi,
For example, in 12/12/2016:

The salesman Jack sold:

12 product A --> Product line L1

11 product B --> Product line L1

9  product C --> Product line L2

The salesman Susan sold:

11 product A --> Productline L1

14 product C --> Productline L2

22 product G ---> Product L3

-->  2 Productlines sold in 12/12/2016 by Lina

---> 3 Productlines sold  in 12/12/2016 by Susan

Because Lina and Susan belong to Direct type  --> measure = SUM(count(distint(productline))/count(distinct(salesmancode) = 3/2=1,5

In order table only contains salesmanCode and productCode, so please attention to the calculation for productline and salesmantype

Regards,
J.

[tringuyenminh92]

Hi @MichaelJackpbi,

I think it's just simple with: 

AVG = DIVIDE(DISTINCTCOUNT('Product'[ProductLine]),DISTINCTCOUNT(SalesMan[SalesManCode]))

 Please check this expression with your data and let me know if it's working or not.

[MichaelJackpbi]

@tringuyenminh92

I dont think the rule was simple like you wirten, please look at the order table- it contains the productcode and the salesmancode but we need to calculate for productline & SalesType that are higher levels.

[Vvelarde]

@MichaelJackpbi

PL = CALCULATE(COUNTROWS(VALUES(Products[ProductLine])))

CountSalesMan = CALCULATE(COUNTROWS(VALUES(Orders[SalesManCode])))

AVG = [PL]/[CountSalesMan]

[MichaelJackpbi]

@Vvelarde Thank for your comment,
It seems like incorrect, but i mean the number of productline / number of salesType( not salemancode)- has any difference between 2 levels?
+Look at the image, i can see the figures for P/L and CountSalesMan were incorrect in total
I see you countrows in Product table to have number of ProductLine but is it true for Order table ?

I really hope you help me to explain more about your rules and give me the pbix file for further references.

Regards,
J.

[tringuyenminh92]

Hi @MichaelJackpbi,

I would like to help your case. Could you please show your expectation in excel file? so i could understand your requirement correctly.

[MichaelJackpbi]

Please help me!.

[MichaelJackpbi]

Please help!.