Count Values Greater Than Measure Average

[FelipeVaz]

Hi,

I have this example in my onedrive. Link: https://1drv.ms/f/s!As8YC7wZr8RKist1_Ql70SsOKar7yA

I create the measure MEDIAN. I would like to count how many itens in the Subcategory column Greater Than the MEDIAN.

Could this calculation be done as a measure, not column?

 

[ralliartur]

Hi,

Yes it can be done in measure, and i have one suggestion:

1. You can use standard MEDIAN() DAX formula
MEDIAN_NEW:=MEDIAN(Tabela1[Average])

2. Now the calculation looks like this:
Above MEDIAN:=
VAR
MEDIANCALC = [MEDIAN_NEW]
RETURN
SUMX(Tabela1; (Tabela1[Average]>MEDIANCALC)*1)

Variables are used to avoid using allexcept() in median calculations

 

[FelipeVaz]

Hi,

Yes it can be done in measure, and i have one suggestion:

1. You can use standard MEDIAN() DAX formula
MEDIAN_NEW:=MEDIAN(Tabela1[Average])

2. Now the calculation looks like this:
Above MEDIAN:=
VAR
MEDIANCALC = [MEDIAN_NEW]
RETURN
SUMX(Tabela1; (Tabela1[Average]>MEDIANCALC)*1)

Variables are used to avoid using allexcept() in median calculations

ralliartur,

MEDIAN() isnt a function in the 2013 DAX Library. Im using this library.

However, how do I implement this new measure?

All of this below I put into the cell in PowerPivot?
Above MEDIAN:=
VAR
MEDIANCALC = [MEDIAN_NEW]
RETURN
SUMX(Tabela1; (Tabela1[Average]>MEDIANCALC)*1)

Ive tried this syntax, resulting in a error.

 

[ralliartur]

ralliartur,
MEDIAN() isnt a function in the 2013 DAX Library. Im using this library.

Sorry, VARS are not in Excel2013 Either

in this case Above MEDIAN:=SUMX(Tabela1; (Tabela1[Average]>CALCULATE([MEDIAN];ALLEXCEPT(Tabela1;Tabela1[Category];Tabela1[Subcategory])))*1)

 

[FelipeVaz]

Sorry, VARS are not in Excel2013 Either

in this case Above MEDIAN:=SUMX(Tabela1; (Tabela1[Average]>CALCULATE([MEDIAN];ALLEXCEPT(Tabela1;Tabela1[Category];Tabela1[Subcategory])))*1)

ralliartur, works fine for me!

Thank you a lot for your support.

When writing in Excel we can use F9 to evaluate step-by-step calculations. Thats different in this case. Why did you use *1 at the end of phrase?? Convert the TRUE/FALSE matrix in 1/0??

 

[ralliartur]

Why did you use *1 at the end of phrase?? Convert the TRUE/FALSE matrix in 1/0??

You are right this is conversion from true/false to numeric 1/0 in order to get sum

The hidden reason - i don't like IF in DAX statements