Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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 )
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |