DAX on Median Calculation

[abhiiimr15]

Hi,

I am struggling with DAX wherein I would like to know the Median Price and Max Price of the set of products from the following data set.

Avg. Cost of the region will be per region ID and is displayed in Avg. Cost in region colum. (=Cost/Product COunt).

Also, Median Price I would like to calculate across all regions

And also the Max Price across all regions.

Any help???

Region ID	Cost	Product Count	Avg. Cost in region
4770208	1	1	1
84393824	48	22	2.18
39386220	28	9	3.11
11043926	115	44	2.61
29057573	20	10	2
34493931	64	23	2.78
81095253	22	13	1.69
38864536	20	11	1.82
69767998	67	26	2.58
77824349	2	1	2
19635856	5	2	2.5
23996317	49	16	3.06
95955920	5	2	2.5
71918671	10	4	2.5
53741679	21	7	3
73467008	45	17	2.65
93243941	9	4	2.25
51472116	16	6	2.67
417901	2	1	2
78973297	47	16	2.94
30917529	5	2	2.5
19655066	37	12	3.08
31727951	45	22	2.05
75585923	7	2	3.5
96504019	26	8	3.25
98073814	3	2	1.5
674899	65	19	3.42
37642492	5	2	2.5
70617906	5	2	2.5

[AlB]

Hi @abhiiimr15 

Not sure I understand fully. Once you have Avg. Cost in region, would it suffice to create two new measures?

MedianPrice = MEDIAN(Table1[Avg. Cost in region])

and

MaxPrice = MAX(Table1[Avg. Cost in region])

[abhiiimr15]

'm sorry if I was not very clear. Actually my requirement is at the following level

Below is the data table of the above screen shot. 

Super Geo	Country	Region ID	Cost	Product Count	Avg. Cost in region	Median at Country level	Median at Super Geo level	Max Price at Country level	Max Price at Super Geo level
A	1	4770208	1	1	1	2.18	2.34	3.11	3.11
A	1	84393824	48	22	2.18
A	1	39386220	28	9	3.11
A	1	11043926	115	44	2.61
A	1	29057573	20	10	2
A	2	34493931	64	23	2.78
A	2	81095253	22	13	1.69	2.50		3.06
A	2	38864536	20	11	1.82
A	2	69767998	67	26	2.58
A	2	77824349	2	1	2
A	2	19635856	5	2	2.5
A	2	23996317	49	16	3.06
A	2	95955920	5	2	2.5
B	3	71918671	10	4	2.5	2.58	2.58	3.00	3.08
B	3	53741679	21	7	3
B	3	73467008	45	17	2.65
B	3	93243941	9	4	2.25
B	4	51472116	16	6	2.67	2.59		3.08
B	4	417901	2	1	2
B	4	78973297	47	16	2.94
B	4	30917529	5	2	2.5
B	4	19655066	37	12	3.08
B	4	31727951	45	22	2.05
C	5	75585923	7	2	3.5	2.88	2.88	3.50	3.50
C	5	96504019	26	8	3.25
C	5	98073814	3	2	1.5
C	5	674899	65	19	3.42
C	5	37642492	5	2	2.5
C	5	70617906	5	2	2.5

[AlB]

You can still use the measures above in table visual where you need to place in rows the level you want to look at: Country or Super Geo