Forum Discussion

Sarevem's avatar
Sarevem
Frequent Visitor
8 years ago
Solved

Group data based on % to total

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 NameYTD Sales% to total
Account 1 10403.1%
Account 26411.9%
Account 35791.7%
Account 45101.5%
Account 54821.4%
Account 64201.2%
Account 74031.2%
Account 83891.1%
Account 93721.1%
Account 103621.1%
Account 113581.1%
Account 123511.0%
Account 132970.9%
Account 142890.9%
Account 152620.8%
Account 162480.7%
Account 172440.7%
Account 182410.7%
Account 192310.7%
Account 202270.7%

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] ) )
    )

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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] ) )
    )