Calculates the sum of an expression evaluated for each row in a table.
|
Quick Coach Video |
What it does? Calculates the sum of an expression evaluated for each row in a table. Syntax SUMX(<table>,<expression>) Returns A decimal number. |
What is the SUMX Function?
The SUMX function in DAX acts like a diligent accountant, summing up values by evaluating an expression across each row of a specified table. Whether you're analyzing sales figures, calculating costs, or aggregating other metrics, SUMX allows you to derive meaningful insights by evaluating expressions row by row before summing them up!
Example 1: Total Pizza Slices Sold
Imagine you’re hosting an epic pizza party, and you want to calculate the total slices sold from all the pizzas ordered. Here’s how your data might look:
|
Pizza Style |
Pizzas Ordered |
Slices per Pizza |
Price per Slice ($) |
|
New York |
3 |
8 |
2 |
|
Detroit |
5 |
10 |
2.50 |
|
Chicago |
2 |
6 |
4 |
|
New Haven |
4 |
8 |
2 |
To find the total revenue from the pizza slices sold, you’d use the SUMX function like this:
Total Slice Revenue =
SUMX(PizzaSalesTable, PizzaSalesTable[Pizzas Ordered] *
PizzaSalesTable[Slices per Pizza] *
PizzaSalesTable[Price per Slice ($)])
Let’s calculate the revenue for each style:
- New York: 3 x 8 x 2 = 48
- Detroit: 5 x 10 x 2.50 = 125
- Chicago: 2 x 6 x 4 = 48
- New Haven: 4 x 8 x 2 = 64
Total Slice Revenue = 48 + 125 + 48 + 64 = 285 🍕🎉
Example 2: Adventure Park Ticket Revenue
Now let’s amp up the excitement! You’re analyzing ticket sales at a thrilling adventure park, where each ticket type has a different price and a certain number of tickets sold. Here’s how your data might look:
|
Ticket Type |
Tickets Sold |
Ticket Price ($) |
Discount (%) |
|
Regular Pass |
50 |
40 |
0 |
|
Fast Pass |
30 |
60 |
10 |
|
VIP Pass |
10 |
100 |
15 |
To calculate the total revenue from ticket sales, considering the discounts, you’d use the SUMX function like this:
Dax
Total Ticket Revenue =
SUMX(AdventureParkSalesTable,
AdventureParkSalesTable[Tickets Sold] *
AdventureParkSalesTable[Ticket Price ($)] *
(1 - (AdventureParkSalesTable[Discount (%)] / 100)))
Let’s break it down:
- Regular Pass: 50 x 40 x (1-0) = 2,000
- Fast Pass: 30 x 60 x (1-0.10) = 1,620
- VIP Pass: 10 x 100 x (1-0.15) = 850
Total Ticket Revenue = 2,000 + 1,620 + 850 = $4,470 🎢💰
Conclusion: Sum Up the Fun with SUMX!
The SUMX function in DAX lets you calculate sums based on dynamic expressions, ensuring you capture every exciting detail! Whether you’re adding up pizza slice sales or thrilling adventure park ticket revenues, SUMX helps you create a deliciously insightful analysis. Let’s keep summing up the fun! 🥳