Creating a Cumulative Sum of a measure in a table without a date field

[data4thewin]

I want to know how many customers are driving 80% of my sales.  I have a sales table for 2016 by Customer ID.  I am wanting to cumulative sum my PCT total column and then identify the customers that are driving 80% of my sales.  

I can't figure out if it is possible to cumulative sum the PctTotalRevenue measure in my table?

Table

Sales

Columns

Customer ID

Revenue

Measures

2016Revenue=SUM('Sales'[Revenue])

2016Rank=RANKX(ALL('Sales'), 'Sales'[2016Revenue])

PctTotalRevenue= DIVIDE(CALCULATE(SUM('Sales'[Revenue])),CALCULATE(SUM('Sales'[Revenue]),ALL('Sales')))

Example of Data

CustomerID                  Revenue            2016Rank   PctTotalRevenue      CumSumPCTTotal

1238                              $5,000                     1                  .19%                        .19%

1236                              $4,700                     2                  .17%                        .36%

1222                              $4,200                     3                  .15%                        .51%

[v-yulgu-msft · Accepted solution]

Hi @data4thewin,

Please try below formula to create a measure:

Running Pct =
DIVIDE (
    CALCULATE (
        SUM ( Sales[Revenue] ),
        FILTER ( ALL ( Sales ), Sales[Revenue] >= MAX ( Sales[Revenue] ) )
    ),
    CALCULATE ( SUM ( Sales[Revenue] ), ALL ( Sales ) )
)

Best regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @data4thewin,

Please try below formula to create a measure:

Running Pct =
DIVIDE (
    CALCULATE (
        SUM ( Sales[Revenue] ),
        FILTER ( ALL ( Sales ), Sales[Revenue] >= MAX ( Sales[Revenue] ) )
    ),
    CALCULATE ( SUM ( Sales[Revenue] ), ALL ( Sales ) )
)

Best regards,
Yuliana Gu

[data4thewin]

In Direct Query mode I get the message that Filter cannot be performed in Query Mode.  In Import mode I get the message that I do not have enough memory to complete this operation. Ideas?  Thanks!

[CheenuSing · Accepted solution]

hi @data4thewin

In a similar exercise I did the following.

a) Measure Sales = SalesData[Sales  Amount]

b)  Measure RankByV

RankByV = IF(HASONEVALUE(Products[ITEM]),
CALCULATE(
    RANKX(ALL(Products[ITEM]), [Sales]),
    VALUES(Products[ITEM]),
    ALL(Products)))

c) Measure Contribution by Products

     Contribution = [Sales] * 100 / Calculate ( [Sales], All(Products))

    Products is a separate dimension table and is linked to ITEM column of SalesData.

d) Measure  CumSalesAllProducts

CumSalesAllProducts = CALCULATE(
SUMX(
    TOPN(
        [RankByV],
        ALL(Products[ITEM]),
        [Sales]),
    [Sales]),
VALUES(Products[ITEM]),
ALL(Products))

e) Measure CumContribution 

          CumContribution = [CumSalesAllProducts] * 100 / Calculate ( [Sales], All(Products))

f) I then created a table report with Description from Products table,  Contributiob measure calculated at (c) above.

g) In the filters included the CumContribution and set the filter to less than 81.

You may have to replace the Products table with your customer table. 

If it worked for you please accept it as a solution and also give KUDOS.

Cheers

CheenuSing