Group data based on % to total

[Sarevem]

Hi! 

I have invoice level sales data - by account and item - for the past 3 years. I have used time intelligence to create measures such as 'latest 26 week sales' and 'year-to-date sales'. 

I've looked at both the percentilex and TopN functions and haven't been able to get them to accomplish my end goal precisely.

Essentially, I want to be able to group data based on % of total. For example: X accounts drove top 25% of sales, X accounts drove the next 25-50% of sales. The number of accoutns or items is always going to vary based on the timeframe I'm looking at or filters I apply limiting me from using TopN. 

I'm assuming I need to use some type of rank formula? I've been rather stumped on the best way to segment my data. 

Account Name	YTD Sales	% to total
Account 1	1040	3.1%
Account 2	641	1.9%
Account 3	579	1.7%
Account 4	510	1.5%
Account 5	482	1.4%
Account 6	420	1.2%
Account 7	403	1.2%
Account 8	389	1.1%
Account 9	372	1.1%
Account 10	362	1.1%
Account 11	358	1.1%
Account 12	351	1.0%
Account 13	297	0.9%
Account 14	289	0.9%
Account 15	262	0.8%
Account 16	248	0.7%
Account 17	244	0.7%
Account 18	241	0.7%
Account 19	231	0.7%
Account 20	227	0.7%

[Zubair_Muhammad · Accepted solution]

Hi @Sarevem

How about a Cumulative % to total....ordered from Account with highest sales to lowest

This will give you an idea of how many Account accumulate upto 25% or 50% etc of the total sales

you can first add a MEASURE to RANK the accounts

RANK =
RANKX (
    ALL ( TableName[Account Name] ),
    CALCULATE ( SUM ( TableName[YTD Sales] ) ),
    ,
    DESC,
    DENSE
)

Then the following MEASURE will give you Cumulative %age to total

Cumulative % to Total =
VAR CurrentRANK = [RANK]
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( TableName[YTD Sales] ),
            FILTER ( ALL ( TableName[Account Name] ), [RANK] <= CurrentRANK )
        ),
        CALCULATE ( SUM ( TableName[YTD Sales] ), ALL ( TableName[Account Name] ) )
    )

[Zubair_Muhammad]

@Sarevem

See File attached here