How to get MEDIAN of a WHOLE column that is a calculated Measure

[Anonymous]

I'm not sure how to approach this.

I need another measure to get the MEDIAN of the WHOLE COLUMN based on a calculated measure (USA|HK).

USA|HK = IFERROR(DIVIDE(MEDIAN('CONTROL_ROOM_TMC2'[usa]), MEDIAN('CONTROL_ROOM_TMC2'[HK])),BLANK())

MEDIAN the whole column of  is = USA|HK 1.265 (ACCORDING TO EXCEL MEDIAN())

PROGRAM	VARIANT		US		HK		US|HK		**Result** needed MEDIAN
1	32		92.510		71.067		1.302		1.265
2	64		97.027		75.093		1.292		1.265
3	64				107.638
4	128		408.861		321.007		1.274		1.265
5	128				340.042
6	128		244.750		224.007		1.093		1.265
7	64		125.325		99.806		1.256		1.265
8	128		107.578
9	64				121.283
10	32		104.203		83.939		1.241		1.265

I would like to have the median to showcase in "**Result** needed MEDIAN" so that I can create conditional formatting against that median

[Anonymous]

I need another measure to get the MEDIAN of the WHOLE COLUMN based on a calculated measure (USA|HK).

USA|HK = IFERROR(DIVIDE(MEDIAN('CONTROL_ROOM_TMC2'[usa]), MEDIAN('CONTROL_ROOM_TMC2'[HK])),BLANK())

MEDIAN the whole column of  is = USA|HK 1.265 (ACCORDING TO EXCEL MEDIAN())

PROGRAM	VARIANT		US		HK		US|HK		**Result** needed MEDIAN
1	32		92.510		71.067		1.302		1.265
2	64		97.027		75.093		1.292		1.265
3	64				107.638
4	128		408.861		321.007		1.274		1.265
5	128				340.042
6	128		244.750		224.007		1.093		1.265
7	64		125.325		99.806		1.256		1.265
8	128		107.578
9	64				121.283
10	32		104.203		83.939		1.241		1.265

I would like to have the median to showcase in "**Result** needed MEDIAN" so that I can create conditional formatting against that median

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create a measure as below:

Country3|Country4 =
IF (
    ISBLANK ( MAX ( 'demo'[Country3] ) ) || ISBLANK ( MAX ( 'demo'[Country4] ) ),
    BLANK (),
    CALCULATE (
        DIVIDE ( MEDIAN ( 'demo'[Country3] ), MEDIAN ( demo[Country4] ) ),
        ALL ( demo )
    )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Fowmy]

@Anonymous 

In Excel and Power BI it returns 0.999443 but not 1.265

[Anonymous]

Hi Fowmy,

I also had that number in my grand total but isn't that counting all 10 rows. I actually only want the values of [US|HK] (nonblanks) which is 6 rows. Does this mean i need to to MEDIAN only the 6 rows?

[Fowmy]

@Anonymous 

If you want to consider only the rows where both US and HK are present then use this as a calculated column:

USA|HK = 
CALCULATE(
    DIVIDE(MEDIAN(CONTROL_ROOM_TMC2[US]), MEDIAN('CONTROL_ROOM_TMC2'[HK])),
    FILTER(
        CONTROL_ROOM_TMC2,
        CONTROL_ROOM_TMC2[US] <> BLANK() && CONTROL_ROOM_TMC2[HK] <> BLANK()
    )
)

[Anonymous]

I'm using "NEW measures". I could not do this in Power Query due to a sorting issue.

Divide = IFERROR(DIVIDE(MEDIAN(Test[US]), MEDIAN(Test[HK])),BLANK())

Median = CALCULATE( DIVIDE(MEDIAN(Test[US]), MEDIAN(Test[HK])),
FILTER( CONTROL_ROOM_TMC2, [Divide] <> BLANK() ))

[Fowmy]

@Anonymous 

The code I shared can be used as a new column in your table under Data View not in Power Query 

[Anonymous]

Hi Fowmy,

I would like to say thank you so much for looking into this. I really appreciate your knowledge of this.

In my data view, i am no able to use "new column" because my data is not align based on what's above so in my data view it's not sorted properly hence why i decided to use "new measure. Would this be considered a flaw in my new measure calculation?

below is a view of my data view, each row will have one cost with ONE country only. My report view is base on the table (previous screen caption).

[Fowmy]

@Anonymous 

I believe the actual data layout that you have is different from the sample you shared so the formula that I made was based on that. 

I suggest you create a small Power BI file that represent exactly your actual file with sample data and share the link here after saving it in One Drive or any other place. 

[Anonymous]

Hi Fowmy,

Were you able to get to my link? 

[Syndicate_Admin · Accepted solution]

Hi @icchung22 ，

Create a measure as below:

Country3|Country4 =
IF (
    ISBLANK ( MAX ( 'demo'[Country3] ) ) || ISBLANK ( MAX ( 'demo'[Country4] ) ),
    BLANK (),
    CALCULATE (
        DIVIDE ( MEDIAN ( 'demo'[Country3] ), MEDIAN ( demo[Country4] ) ),
        ALL ( demo )
    )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create a measure as below:

Country3|Country4 =
IF (
    ISBLANK ( MAX ( 'demo'[Country3] ) ) || ISBLANK ( MAX ( 'demo'[Country4] ) ),
    BLANK (),
    CALCULATE (
        DIVIDE ( MEDIAN ( 'demo'[Country3] ), MEDIAN ( demo[Country4] ) ),
        ALL ( demo )
    )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi & Good Morning Fowmy,

Please let me know if you can access this link.
https://drive.google.com/file/d/1321fxPGTEbQNzODsdxPRUowgbOJFQQ5T/view?usp=sharing

The main focus could be figuring out how to ignore blanks & zeros so that the median can return the correct number.