cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
data4thewin Frequent Visitor
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
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
Super User

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

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

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
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.
Highlighted
data4thewin Frequent Visitor
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
Super User

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

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

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

Proud to be a Datanaut!