Calculate median of a measure and a calculated column based on median

[Argha]

Hi,

I am trying to calculate median of a measure and then use both measure and median to create a calculated column. Appreciate any help on this.

I have the following view as below with some sample data

Program Name	Team Name	Topic Name	Question	Answered Correctly
A	AA	W	ABC ?	Yes
B	BB	X	DEF ?	Yes
C	CC	Y	GHI ?	No
D	DD	Z	JKL ?	Yes

Created a mesaure 'Correct Percentage' as below
Questions Answered Correctly = Calulate(CountRows(View), view(Answered Correctly) = "Yes")
Total = 

Countrows(view)

Return Divide(Questions Answered Correctly, Total, 0)

I need to calculate Median of the above measure, i.e., Median of 'Correct Percentage' and then create a calculated column 'Category' with the following logic

If Correct Percentage >= Median then 'High Correct %' else 'Low Correct %'

The purpose of creating this 'Catogory' calculated column is to create a Matrix visual as below.

Topic Name	High Correct %	Low Correct %
W	76%
X	80%
Y		32%
Z		18%

I am facing challenge in calculaing the "median" out of 'Correct Percentage' measure and then the 'Category' column to dislay in above format.
Any help would be really appreciated.

Thanks in Advance.

Regards,

Argha

[Kedar_Pande]

Create a summarized table for your median calculation:

Program Summary =
SUMMARIZE(
'View',
'View'[Program Name],
"Correct %", [Correct Percentage]
)

Then create a measure for the median:

Overall Median =
MEDIANX('Program Summary', [Correct %])

 

For your matrix, don't use a calculated column. Create this measure instead:

Category Result =
IF([Correct Percentage] >= [Overall Median], "High Correct %", "Low Correct %")

@Argha

[Argha]

@Kedar_Pande - Thank you for your response.
I have already tried this way before and it doesn't work as expected. 
Median is not calculated correctly. If both 'Correct percentage' and 'Median' is pulled in one table visual, the median value is same as the 'correct percentage' for a particular row.

For example, for Topic 'W' if the 'correct percentage' is 0.75, the median too shows as 0.75. However, the requirement is to have the median calculated considering all values of 'Correct Percentage' column

[MFelix]

Hi @Argha ,

For this you need to use a variable with a temporary table something similar to this:

High Correct % =
Var temptable = ADDCOLUMNS( ALLSELECTED(Table[Topic]),"CorrectPercentage", [Correct Percentage])
)

Var medianvalue = MEDIANX ( temptable, [CorrectPercentage])

Return
IF([Correct Percentage] >= medianvalue, [Correct Percentage])


Low Correct % =
Var temptable = ADDCOLUMNS( ALLSELECTED(Table[Topic]),"CorrectPercentage", [Correct Percentage])
)

Var medianvalue = MEDIANX ( temptable, [CorrectPercentage])

Return
IF([Correct Percentage] < medianvalue, [Correct Percentage])

Be aware that depending on your model the part of the ALLSELECTED may need to be changed by a values or something similar.

[Argha]

Thank you for your quick response.

I quite did not get your statement "Be aware that depending on your model the part of the ALLSELECTED may need to be changed by a values or something similar."

From model perspective the sample view mentioned here acts as a fact table and is having a join with a dimension table.

Also, i did try creating 2 calculated columns as you suggested - 'High Correct %' and 'Low Correct %'. The former is not retrieving any value and while creating the later i get 'Circular Dependency' error.

[MFelix]

Hi @Argha ,

What I have sent you is a measure not a column.

What I mean with the ALLSELECTED part is that this may need to be changed to another type of values selecting like ALL or VALUES.

Basically the measure is based on a virtual table where you calculate the median só that you can check if the % is above or below that.

If you want I can try and send you and examples.

[v-achippa]

Hi @Argha,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MFelix and @Kedar_Pande for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Argha,

We wanted to kindly follow up to check if the solution provided by the user for the issue worked? Is your issue resolved? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Argha]

Hi @v-achippa 

Sorry for the late response. I got busy with other priorities.

Unfortunately both solutions didn't work for me.
1st solution @Kedar_Pande - I have already tried this myself before posting here. This doesn't work as per my requirement.
2nd solution @MFelix - It works partially with the below tweaked DAX logic. 

Low % Correct Answer =

var temptable =

SUMMARIZECOLUMNS('Table'[Program Name], 'Table'[Topic Name],'table'[Question],

    "Correct %", [Correct Percentage]

)

Var Correctper_medianvalue = MEDIANX(ALL('Table'), 'Table'[Correct %])

RETURN

IF([Correct Percentage] < Correctper_medianvalue , [Correct Percentage])

With the above dax i do get the median value but it doesn't interact with 'Program Name' slicer.
The median value remains constant , i.e., it retains the same value for all programs, which should not be the case.

Regards,

Argha

[MFelix · Accepted solution]

Hi @Argha ,

The value is getting the same value on all the values because you are using the ALL statement that removes any filter context on your calculation making it program agnostic.

You need to make it with a ALLSELECTED option or a FILTER in order to get the values for the programs you need.

Can you give a more complete example on your needs, with the example you gave the result got correct however it only has for lines, and there is no information about dimension or relationships on your model wich make it difficult to perceive any other filter context that may impact the calculation.