Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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])
'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 |
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
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |