How to get MEAN/AVERAGE 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 MEAN/AVERAGE of the WHOLE COLUMN based on a calculated measure (USA|HK).

USA|HK = IFERROR(DIVIDE(SUM('CONTROL_ROOM_TMC2'[BR_JAG]), SUM('CONTROL_ROOM_TMC2'[MX])),BLANK())

Average the whole column of  is = USA|HK 1.25

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

USA|HK Measure =

COALESCE ( AVERAGEX ( 'Table', DIVIDE ( 'Table'[USA], 'Table'[HK] ) ), "" )

result measure =
IF (
OR (
SELECTEDVALUE ( 'Table'[USA] ) = BLANK (),
SELECTEDVALUE ( 'Table'[HK] ) = BLANK ()
),
BLANK (),
CALCULATE ( [USA|HK Measure], ALL ( 'Table' ) )
)

https://www.dropbox.com/s/lbbqgj9nxlogplg/icchung.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi Jihwan,
Thank you for the solution. Very helpful.

I ended up using this DAX based on your guidance.

MEAN =

IF (CALCULATE([BR_JAG|MX]) = BLANK (),

BLANK (),

AVERAGEX(

FILTER(

ALLSELECTED( CONTROL_ROOM_TMC2[PMD_PROGRAM_NAME] ),

CONTROL_ROOM_TMC2[PMD_PROGRAM_NAME] = ( CONTROL_ROOM_TMC2[PMD_PROGRAM_NAME] )

),

[BR_JAG|MX]

)

)

[Anonymous]

Hi Jihwan,

Thank you for helping out on this. I'm inputting your calculations but all my results are returning blank.

[Jihwan_Kim · Accepted solution]

Thank you for your feedback.

Please share your sample pbix file's link here, then I can try to look into it to find out the cause of the difference.

[Anonymous]

Dear Jihwan,

Thank you for your assistance. I tested by adding "SUM" to show up the numbers but the SUM is causing the incorrect avg. The 1st 4 columns are linked to SQL.

additional note: when i SELECTEDVALUE ( 'CONTROL_ROOM_TMC2'[BR_JAG]) = BLANK () it actually return as BLANK. So the column is saying it's blank. I just tested all columns of BR_JAG & MX and it's all blank.

COALESCE ( AVERAGEX (CONTROL_ROOM_TMC2, DIVIDE (SUM('CONTROL_ROOM_TMC2'[BR_JAG]), SUM('CONTROL_ROOM_TMC2'[MX])) ), "" )

Average =

IF (

OR (

SUM( CONTROL_ROOM_TMC2[BR_JAG] ) = BLANK(),

SUM( CONTROL_ROOM_TMC2[MX] ) = BLANK()

),

BLANK(),

CALCULATE ( [BR_JAG|MX], ALL ( CONTROL_ROOM_TMC2 ) )

)

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

USA|HK Measure =

COALESCE ( AVERAGEX ( 'Table', DIVIDE ( 'Table'[USA], 'Table'[HK] ) ), "" )

result measure =
IF (
OR (
SELECTEDVALUE ( 'Table'[USA] ) = BLANK (),
SELECTEDVALUE ( 'Table'[HK] ) = BLANK ()
),
BLANK (),
CALCULATE ( [USA|HK Measure], ALL ( 'Table' ) )
)

https://www.dropbox.com/s/lbbqgj9nxlogplg/icchung.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi Jihwan,

Can I use the same structure to get Median?

MEDIANX(

SUMMARIZE(CONTROL_ROOM_TMC2,CONTROL_ROOM_TMC2[PMD_PROGRAM_NAME],"xxx",[BR_JAG|MX]),

[BR_JAG|MX])

Right now it doesn't give me the correct Median based on my [BR_JAG|MX]

[Jihwan_Kim]

Hi, @Anonymous 

Please try something like the below.

newmeasures =
MEDIANX (
SUMMARIZE (
CONTROL_ROOM_TMC2,
CONTROL_ROOM_TMC2[PMD_PROGRAM_NAME],
"xxx", [BR_JAG|MX]
),
[xxx]
)

thank you.

[Anonymous]

Hi Jihwan,

Did you see my link? 

[Anonymous]

Hi & Good Day Jihwan,

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.