Calculate median

[Anonymous]

I have a table called Award-Deny Table  as follows:

Days Worked     Received Date        Action Date

    2                       

    5

    3

  34

  39

  39

  40

  93

  95

  88

And I want to find Median of range of values 0-30 days, 31-60 days, 61-90 days, and 90+ days.  I saw on-line where I might can use a comparison table with min and max values and use that in my measure.  I am new to Power BI by the way.

[Anonymous]

hi @Anonymous 

Welcome to the world of Power BI 🙂

are you trying to see how many times days worked happen by a bucket value? for example

0-30 days 3

31-60 days 4

61-90 days 1

91+ days 2

and have this data in a new table?

[Anonymous]

Just wanting to write a DAX statement with filters to find median of each of those buckets.  I saw wher in one filter you can read the valuse in the members of the whole column and compare with min and max values.  The table I defined to compare to:  Range value   Min     Max

                                                                                                                                      0-30            0            30

[Anonymous]

I'm sorry , I guess I hit transmit before I finished.

The compare table I created to  use in the filter is named Group Ranges:

  Range Value    Min        Max

     0-30                    0               30

     31-60               31              60

     61-90               61              90

        90+                 91          99999

I know the 90+ doesn't seem right in Range Value but that's how the specs described it.

I'm just trying to calculate the Median of the values of Days Work field in the table in first post for all the ones in 0-30 range, 31-60 range and so on.  I know I will have to use a filter to load values to compare to the Compare table and have the median for each range in a visual.  Like I said I'm very new to Power BI.

[Anonymous]

Hi,

Yeah so you can do it.

You need to create a new column for each "Bucket" 0-30, 31-60 etc with the following DAX

0 - 30 = IF(data[Days Worked] >=0 && data[Days Worked] <=30,data[Days Worked],BLANK())

31 - 60 = IF(data[Days Worked] >=31 && data[Days Worked] <=60,data[Days Worked],BLANK())

etc

This will then populate the cell if it meets the bucket critera, and if not it will remain blank.

You will end up with 4 new columns in your table, for for each bucket

You can then create a measure to get the Median for each column.

Median 0 - 30 = MEDIAN(data[0 - 30])

Median 31 - 60 = MEDIAN(data[31 - 60])

etc

you can then use this measure in your report

You can use other statistic measure too, like average if you wanted to too.

If this solves your query please mark this as your solution and help others find it quicker 🙂

[Anonymous · Accepted solution]

Thank You! That got me to thinking to add another calculated column. Being from COBOL, I was more comfortable using 'if' instead of 'case' statement: Bucket Range = IF(AND (DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] >= 0, DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] <= 30), "0-30",IF(AND(DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] >= 31, DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED]<= 60),"31-60",IF(AND(DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] >= 61, DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] <= 90), "61-90",IF(AND (DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] > 90, DO_APPL_AWARD_DENIED_PREV_MO[DAYS WORKED] <= 99999), "90+")))). That worked nicely in my column and I just laid one visual over another and got a great visual that worked nicely. This is order of visuals I used: 1)Drug Days Work and then clicked Median option 2)Bucket Range over #1 3)A Status field from main table over above 4)My Day Range Group And it seemed it magically appeared w/o writing one single amount of code. Sorry I could not get the visual to post here. Thanks Again!