Categories: DAX
Tags:

10 DAX-based analytical questions on USA Pizza Orders Dataset

Based on your PizzaOrders table that we discussed in previous example : Check Here
Here are 10 DAX-based analytical questions along with their DAX formulas, including one involving RANKX, with explanations of the arguments and expected results:

✅ 1. Total Orders Count

Question: How many total pizza orders were made?

Total Orders = COUNTROWS(PizzaOrders)

Expected Result: Returns the number of rows (orders) in the PizzaOrders table.

✅ 2. Total Distance Covered

Question: What is the total delivery distance covered in km?

Total Distance = SUM(PizzaOrders[Distance (km)])

Expected Result: Returns the sum of all delivery distances.

✅ 3. Average Delivery Duration

Question: What is the average delivery duration?

Average Delivery Time = AVERAGE(PizzaOrders[Delivery Duration (min)])

Expected Result: Average of all delivery times in minutes.

✅ 4. Orders by Pizza Type

Question: How many orders were placed for each pizza type?

Orders by Pizza Type = COUNTROWS(PizzaOrders)

Use in a visual with Pizza Type as a dimension.
Expected Result: Bar chart/table showing count per pizza type.

✅ 5. Delayed Orders Count

Question: How many deliveries were delayed?

Delayed Orders = CALCULATE(COUNTROWS(PizzaOrders), PizzaOrders[Is Delayed] = TRUE())

Expected Result: Number of orders where the delivery was delayed.

✅ 6. On-Time Delivery Rate (%)

Question: What is the percentage of orders delivered on time?

On-Time Delivery % = 
DIVIDE(
    CALCULATE(COUNTROWS(PizzaOrders), PizzaOrders[Is Delayed] = FALSE()),
    COUNTROWS(PizzaOrders),
    0
)

Expected Result: Percentage of orders delivered on time.

✅ 7. Top 5 Restaurants by Order Volume

Question: What are the top 5 restaurants by number of orders?

Restaurant Order Count = COUNTROWS(PizzaOrders)

Use in a visual with Restaurant, sorted descending by this measure and limited to Top 5.

✅ 8. Average Delay per Restaurant

Question: What is the average delay per restaurant?

Average Delay = AVERAGE(PizzaOrders[Delay (min)])

Use in a matrix with Restaurant as rows.

✅ 9. Rank Restaurants by Speed (RANKX)

Question: Rank restaurants based on their average delivery time (fastest = Rank 1)

Rank by Speed = 
RANKX(
    ALL(PizzaOrders[Restaurant]),
    CALCULATE(AVERAGE(PizzaOrders[Delivery Duration (min)])),
    ,
    ASC,
    Dense
)

Arguments Explained:

  • ALL(PizzaOrders[Restaurant]): Ignores filters to rank globally.
  • CALCULATE(...): Computes average delivery time for each restaurant.
  • ASC: Ranks from fastest to slowest.
  • Dense: No rank gaps (e.g., 1, 2, 2, 3).

Expected Result: A number for each restaurant indicating its speed rank.

✅ Full Syntax of RANKX

RANKX(<table>, <expression>, [value], [order], [ties])

✅ Breakdown of Parameters

ParameterDescription
<table>The table over which ranking will happen. Usually use ALL() to remove filters.
<expression>The expression to evaluate (what you’re ranking by).
[value](Optional) The value to rank. If blank, it’s calculated automatically based on current row context.
[order]ASC (default) or DESC.
[ties]Skip (default) or Dense. Defines how tied ranks are treated.

✅ So What Happens if [value] is Left Blank?

When you leave the [value] parameter blank, Power BI automatically uses the current row context of the table and evaluates the expression for that row. This is very common and usually fine for 99% of use cases.

-- Simplified version
Rank by Speed = 
RANKX(
    ALL(PizzaOrders[Restaurant]),
    CALCULATE(AVERAGE(PizzaOrders[Delivery Duration (min)])),
    ,
    ASC,
    DENSE
)

Here, the blank [value] means:

“Use the average delivery time for the current restaurant in the visual.”

✅ When Would You Use the [value] Parameter?

You’d explicitly use [value] if you’re not relying on the current row context. For example:

Rank for "Dom" = 
RANKX(
    ALL(PizzaOrders[Restaurant]),
    CALCULATE(AVERAGE(PizzaOrders[Delivery Duration (min)])),
    CALCULATE(AVERAGE(PizzaOrders[Delivery Duration (min)]), PizzaOrders[Restaurant] = "Dom"),
    ASC,
    DENSE
)

Here:

  • You’re manually specifying the value you want to rank: the average delivery time for "Dom".
  • This can be useful when you’re not using visuals with rows, and want to compute the rank of a specific item.

✅ Summary

ScenarioWhat to Put in [value]
In visuals (e.g. matrix or table) where each row is a RestaurantLeave it blank – uses row context
You want to rank a specific item (like “Dom”) in a measure or cardProvide the specific value explicitly

✅ RANK() Function in New DAX (Power BI Desktop 2023+)

RANK([Ties], [Relation], [OrderBy], [Blanks], [PartitionBy], [MatchBy], [Reset])

This new RANK() is a scalar function (returns a number) that works like SQL’s RANK() window function — and it’s very useful for ranking in tables or visuals directly with complex logic.

✅ Explanation of Parameters:

ParameterDescription
Ties"Dense" (1,2,2,3) or "Skip" (1,2,2,4). Similar to RANKX behavior.
RelationTable over which rank is being calculated.
OrderByColumn or expression to rank by.
BlanksHow to handle blanks: "Top" or "Bottom".
PartitionByColumn(s) to reset rank for each group (like GROUP BY).
MatchByOptional, for grouping rows with same value for rank purposes.
ResetOptional boolean or expression to reset the rank (e.g., by slicer/page filter).

Here is an example to show tables ranked by Pizza Delay

Calculated Table 
Ranked by Pizza Delay = ADDCOLUMNS(PizzaOrders, "DelayRank", RANK(DENSE, PizzaOrders, ORDERBY(PizzaOrders[Delay (min)]), LAST))

✅ 10. Monthly Order Trend

Question: Show number of orders by month.

Monthly Orders = COUNTROWS(PizzaOrders)

Use with Order Month on X-axis for trend analysis.