Forum Discussion
Calculate median
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.
- 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!
5 Replies
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.