Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Sep 2014
    Location
    Porto Alegre/Brazil
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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?

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by ralliartur; Apr 25th, 2018 at 08:56 AM.

  3. #3
    New Member
    Join Date
    Sep 2014
    Location
    Porto Alegre/Brazil
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Values Greater Than Measure Average

    Quote Originally Posted by ralliartur View Post
    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.

  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Values Greater Than Measure Average

    Quote Originally Posted by FelipeVaz View Post
    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)

  5. #5
    New Member
    Join Date
    Sep 2014
    Location
    Porto Alegre/Brazil
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Values Greater Than Measure Average

    Quote Originally Posted by ralliartur View Post
    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??

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Values Greater Than Measure Average

    Quote Originally Posted by FelipeVaz View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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