cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative % of Total Measure

Hey All,

Was wondering if anyone could help me create a measure to dynamically calculate Cumulative % of Total Revenue.

 Product Total Revenue Product Rank % of Total Cumulative % of Total Product 1 \$        100,000 1 30% 30% Product 2 \$          75,000 2 23% 53% Product 3 \$          50,000 3 15% 68% Product 4 \$          25,000 4 8% 76% Product 5 \$          20,000 5 6% 82% Product 6 \$          18,000 6 5% 87% Product 7 \$          15,000 7 5% 92% Product 8 \$          10,000 8 3% 95% Product 9 \$            9,000 9 3% 98% Product 10 \$            8,000 10 2% 100% Total \$        330,000 1 100% 100%

I created this measure:

Cumulative % of Revenue =
VAR SalesRT= Calculate(SUM(Table1[Revenue]),FILTER(
ALLSELECTED(Table1[Product]),
ISONORAFTER(Table1[Product],MAX(Table1[Product]),DESC)))
VAR totSales= CALCULATE(SUM(Table1[Revenue]),ALLSELECTED(Table1))

Return
Divide(SalesRT,totSales,0)
This measure worked, but its returning Cumulative % of Total based on Default table sorting, and not by Revenue rank. If anyone has any idea how to alter this, or a different measure that would be greatly appreciated!

1 ACCEPTED SOLUTION
Super User

You need to sort by Total Revenue rather than by product.

Try this:

``````Cumulative % of Revenue =
VAR MinRevenue = MINX ( VALUES ( Table1[Product] ), [Total Revenue] )
VAR SalesRT =
CALCULATE (
SUM ( Table1[Revenue] ),
FILTER ( ALLSELECTED ( Table1[Product] ), [Total Revenue] >= MinRevenue )
)
VAR totSales = CALCULATE ( SUM ( Table1[Revenue] ), ALLSELECTED ( Table1 ) )
RETURN
DIVIDE ( SalesRT, totSales, 0 )``````
Super User

You need to sort by Total Revenue rather than by product.

Try this:

``````Cumulative % of Revenue =
VAR MinRevenue = MINX ( VALUES ( Table1[Product] ), [Total Revenue] )
VAR SalesRT =
CALCULATE (
SUM ( Table1[Revenue] ),
FILTER ( ALLSELECTED ( Table1[Product] ), [Total Revenue] >= MinRevenue )
)
VAR totSales = CALCULATE ( SUM ( Table1[Revenue] ), ALLSELECTED ( Table1 ) )
RETURN
DIVIDE ( SalesRT, totSales, 0 )``````

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!