Min of Median Measure Within Category

[TM_]

I have created a measure to calculate the median of a set of values per sub-category.

I would like to now have the min. of the medians of those sub-categories - example below.

Any idea how I can achieve this in Power BI w/DAX?

Thank you.

FYI - Tried this method but not returning the desired values: 

Measure 2 =
Calculate(
MINX(Test,[Measure]),
ALLEXCEPT(Test,Test[Category])

[rajendraongole1]

Hi @TM_  - Create a measure to calculate the median for each subcategory

Median_Per_Subcategory =
MEDIANX(
FILTER(
Test,
Test[Sub Category] = MAX(Test[Sub Category])
),
Test[Value]
)

you can create another measure to calculate the minimum of the medians for each category:

Min_Of_Medians =
MINX(
SUMMARIZE(
Test,
Test[Sub Category],
"Median", [Median_Per_Subcategory]
),
[Median]
)

Now, you can add the Min_Of_Medians measure to your visual or card to display the desired minimum of the medians for each category.

This approach should return the expected values you highlighted in the Expected Value column (e.g., 2 for Category A, 8 for Category B, and 14 for Category C).

[TM_]

Hi @rajendraongole1  - many thanks for your reply!

I tried this method and the results were as follows:

How do I amend to make it so that the Min_Of_Medians will show up as:

- 2 consistently for Category A, Sub-Categories a,b,c rather than 2,4,6. 

- 8 consistently for Category B, Sub-Categories a,b,c rather than 8,10,12. 

- 14 consistently for Category C, Sub-Categories a,b,c rather than 14,16,18.

My end goal here is to then use the Min_Of_Medians measure as a conditional formatting tool to colour the minimum value in the Median_Per_Subcategory per Category. Essentially the below:  

Thank you. 

[TM_ · Accepted solution]

I've resolved by adding on: 

Min_Of_Medians =

CALCULATE(

MINX(

    SUMMARIZE(

        'Test',

        'Test'[Subcategory]

            ),

[Median]

),

ALLEXCEPT('Test', 'Test'[Category])

)

Then I created another measure for conditional formatting:

 IF(

    [Median] = [Min_Of_Medians],

    1,

    BLANK()

 )

And utilised this to colour the background of only the min. values.

Thanks again for your help!