Median of a column in Table Visualization

[Prashasti]

Hello,

I have table which has iteration and points covered in each iteration as shown below. I need to group the table as shown in figure 2. The second table contains distinct iterations and total points covered in that iteration. Is there a way to calculate the median of these total points covered in Power BI? I need to use the median for further calculation.

Thanks,

Prashasti

[LivioLanzo · Accepted solution]

Hi @Prashasti

you van calculate the median like this:

=
MEDIANX (
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Iteration] ),
        "Total Points", 1.0 * CALCULATE ( SUM ( Data[Points] ) )
    ),
    [Total Points]
)

[LivioLanzo · Accepted solution]

Hi @Prashasti

you van calculate the median like this:

=
MEDIANX (
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Iteration] ),
        "Total Points", 1.0 * CALCULATE ( SUM ( Data[Points] ) )
    ),
    [Total Points]
)

[Prashasti]

Thanks @LivioLanzo, It worked.. I had another doubt. Is it possible to divide the total points of each iteration by the median calculated in this measure? ( In this case 32/38) I need this to calculate the percentage for iteration.

TIA.

[v-cherch-msft · Accepted solution]

Hi @Prashasti

You may try with below measure.

Media =
MEDIANX (
    SUMMARIZE ( Data, Data[Iteration] ),
    CALCULATE ( SUM ( Data[Points] ) )
)

Percent =
DIVIDE ( SUM ( Data[Points] ), CALCULATE ( [Media], ALL ( Data ) ) )

Regards,

Cherie

[Prashasti]

Hey @v-cherch-msft,

In my actual table there are many iterations and I need to display the top 6 iterations and get the score for them. I modified your percent solution to use AllSelected() instead of All() as suggested by you. However, in my graph visualization(Custom Column Chart), upon selecting a particular iteration,the value of percent changes to 1 for all iterations. 

Hoping you could help out here.

Thanks,

Prashasti

[Prashasti]

Thank you @v-cherch-msft , the measure worked perfectly.