DAX Measures to Calculate the Median and Identify if Any of the Values Are Less Than 75% Of It

[Snook]

Hi,

I have some product data and I'd like to calculate what the median is (for each product) and show it in a pivot table alongside the individual supplier data. I'd also like another column to identify if any of the individual values (for that product) are less than 75% of the median, which I will use as a filter criteria to identify any values that need investigating. For example, the median of Product A is 40. The filter column should indicate 'Yes' if any of the individual values (for that product) are less than 30. I've provided some sample data and a screenshot of what I'm looking to achieve.

Product	Supplier	Rate
A	Supplier 1	66.00
A	Supplier 2	21.00
A	Supplier 3	40.00
A	Supplier 4	82.00
A	Supplier 5	37.00
B	Supplier 1	87.00
B	Supplier 2	58.00
B	Supplier 3	42.00
B	Supplier 4	59.00
B	Supplier 5	75.00
C	Supplier 1	47.00
C	Supplier 2	90.00
C	Supplier 3	36.00
C	Supplier 4	71.00
C	Supplier 5	46.00
D	Supplier 1	73.00
D	Supplier 2	14.00
D	Supplier 3	32.00
D	Supplier 4	48.00
D	Supplier 5	93.00
E	Supplier 1	44.00
E	Supplier 2	53.00
E	Supplier 3	45.00
E	Supplier 4	85.00
E	Supplier 5	88.00
F	Supplier 1	25.00
F	Supplier 2	78.00
F	Supplier 3	18.00
F	Supplier 4	87.00
F	Supplier 5	82.00
G	Supplier 1	84.00
G	Supplier 2	16.00
G	Supplier 3	41.00
G	Supplier 4	96.00
G	Supplier 5	43.00
H	Supplier 1	92.00
H	Supplier 2	32.00
H	Supplier 3	96.00
H	Supplier 4	50.00
H	Supplier 5	38.00

If you have any queries, give me a shout.

Thanks in advance,

Snook

[Snook]

If it helps, the link below will take you to the sample workbook I created when I tried to apply your solution.

Link (to sample workbook) 

[Snook]

Thanks for the swift response ThxAlot

I've been trying to apply your solution to my Excel workbook, but I'm struggling to make it work. I downloaded PowerBI Desktop to view the file you attached, but I keep receiving the same error message (see image) when I try to apply the same approach to my Excel workbook. Any idea on where I'm going wrong? If it helps, I've attached a copy of the sample file I've been using, along with my attempt at implementing your solution.

Link 

Regards,

Snook

[ThxAlot]

Md filter.pbix

[Snook]

Thanks for the swift response ThxAlot

I've been trying to implement your solution into my Excel workbook but I'm struggling to make it work. I downloaded PowerBI Desktop to view the file you attached, but when I try to apply the same approach to my Excel workbook I receive the following error message (see image). Any idea where I'm going wrong?

Regards,

Snook