Thread: Count Values Greater Than Measure Average Thanks: 0 Likes: 0

1. Count Values Greater Than Measure Average

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?  Reply With Quote

2. Re: Count Values Greater Than Measure Average

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  Reply With Quote

3. Re: Count Values Greater Than Measure Average Originally Posted by 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
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.  Reply With Quote

4. Re: Count Values Greater Than Measure Average Originally Posted by FelipeVaz 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)  Reply With Quote

5. Re: Count Values Greater Than Measure Average Originally Posted by ralliartur 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??  Reply With Quote

6. Re: Count Values Greater Than Measure Average Originally Posted by FelipeVaz 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  Reply With Quote

User Tag List

Tags for this Thread

count, dax, greater than median, measure, need help  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•