cancel
Showing results for
Did you mean:
Frequent Visitor

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

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%

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Creating a Cumulative Sum of a measure in a table without a date field

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

## Re: Creating a Cumulative Sum of a measure in a table without a date field

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
3 REPLIES 3
Community Support Team

## Re: Creating a Cumulative Sum of a measure in a table without a date field

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Creating a Cumulative Sum of a measure in a table without a date field

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!

Super User

## Re: Creating a Cumulative Sum of a measure in a table without a date field

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!