Getting statistics for quarters of table entries

[Christianvs]

Hi

I have a table with the columns [ID] and [Days] among others:

ID	Days
45	2
57	4
58	2
67	0
82	7
99	7
102	10
107	5

I wish to sort the column with respect to Days (for lowest to highest), make 4 groups (equally sized if possible and else with +-1 entries for one or more of the groups. As long as all entries are accounted for in the 4 groups it doesnt matter which has one entry more or less) and get the minimum, maximum and mean Days for each group. It has to be a DAX measure such that if the data is filtered on some other column(s) the groups will take that into account. I tried a whole lot of different measures using TOPN amongst other functions, but I just haven't been able to make it work correctly.

For the above example the sorted list should be:

ID	Days
67	0
45	2
58	2
57	4
107	5
82	7
99	7
102	10

and the correct measures should yield:
Group 1 (first 25%): Min=0, Max=2 and Mean=1

Group 2 (from 25% to 50%): Min=2, Max=4 and Mean=3

Group 3 (from 50% to 75%): Min=5, Max=7 and Mean=6

Group 4 (last 25%): Min=7, Max=10 and Mean=8.5


My latest try for the mean of group 2, which don't yield the correct, was:

Q2_Mean_Days = 
VAR Table_days =
    FILTER(
        ALLSELECTED('DB1'),
        NOT ISBLANK('DB1'[Days])
    )
VAR AmountQ2 = [QuarterAmount] // Rounded number that should give a quarter of total entries

VAR Q2_Upper =
    TOPN(
        2 * AmountQ2 ,
        Table_days,
        'DB1'[Days],
        ASC
    )
VAR Q1_Table =
    TOPN(
        AmountQ2 ,
        Table_days,
        'DB1'[Days],
        ASC
    )
VAR Q2_Table =
    EXCEPT(Q2_Upper, Q1_Table)
RETURN
    AVERAGEX(
        Q2_Table,
        'DB1'[Days]
    )

Thanks

[amitchandak]

@Christianvs , You can follow how I have done ABC, by changing % 
Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4

Or quartiles 
https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/

https://www.youtube.com/watch?v=U1KdH-2ort8

[Christianvs]

I'm afraid I can't get this working with my problem either 😕

I'm running into some uniqueness problems witht he function WINDOW as the Days column has lots of the same entries. I know for a fact that the highest value og Q1 and the lowest of Q2 should both be 4 days, but I can't get it to correctly show.
It might be that I simply can't get it working using WINDOW but others could

[amitchandak · Accepted solution]

@Christianvs , In Window function we can use more than one order by to break ties 

like orderby([Measure], desc, [Brand], asc) 

where brand is is column I have used in relation of window  

CALCULATE([Net], WINDOW(1,ABS, 0, REL, ADDCOLUMNS(ALLSELECTED('Item'[Brand])),ORDERBY([net],desc, [Brand], asc)))

Also, consider rownumber with count

% =

Divide(

ROWNUMBER(ALLSELECTED(Item[Brand]),ORDERBY([Net],DESC, [Brand], asc)). calculate(countrows(values(Item[Brand])), allselected()) )

Now this will give % between 0 to 1, which you  can use in a percentile 

Use summarize for more than one column with allselected on fact 

Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

[Christianvs]

Thanks Amit. Got it working now 🙂

[Christianvs]

I'll take a look at your youtube, thanks.

I've tried with quartiles but the problem is that i need a number (Days) for the first and last entry in a quartile and I haven't been able to get that. But if you have a way of getting the entry position using quartiles I would be very interested in hearing so