Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
data4thewin
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
v-yulgu-msft
Employee
Employee

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.

View solution in original post

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!

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

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.

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!

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.