Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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%
Solved! Go to 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
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
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |