cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors