Power BI Row Context
Many people often confuse Row Context with Filter Context because of the rows of data in a Tabular visualization. For a deeper understanding of Row Context, check out the article below:
Row Context:
Row Context refers to the current row in an iterative table, on which a formula is being applied.
Notes to Keep in mind:
- Calculated Columns have a built-in Row context.
- Measures do not have a built-in row context, but you can introduce a row context by using function like:
- SUMX()
- MAXX()
- MINX()
Explanation through story telling:
It is ‘know your classmate day’, and excitement buzzed through the classroom as students proudly displayed their most cherished items. Sarah showcased her colorful pet chameleon, Max flaunted his well-worn skateboard, while Timmy was sitting with a grin on his face, waiting for someone to ask about the impressive collection of marbles spread out on his desk.
The colorful marble collection caught the attention of all the kids at school, some even tried to grab a marble or two, but Timmy wasn’t that easy going with his collection, he insisted that whoever wants a marble can state its color and size and Timmy will get it and sell it to them during breaktime.
Business was thriving and it didn’t take long until Timmy was known as the biggest marble dealer across all the 6th grade students, but things were getting out of hand, orders were coming in day and night that Timmy had no choice but to list them down in an Excel sheet.
Timmy wanted to use the profit to buy a ceramic display for his marble collection, but first of all he wanted to determine his biggest customer and his biggest order, so he could write a personal letter for each, thanking them for giving him the chance to boost his hobby to the next step.
Timmy read the notes, that’s why he knew that are two ways upon which he can approach this problem to find a solution either by using:
- Calculated Columns
- Measures
Solution A: Using Measures
As stated in the notes, Measures do not have their own built-in Row Context and you must introduce it using a function, since Timmy wants to calculate the sum of profit gained from each of his customers, Timmy will use the SUMX function:
To calculate the profit Timmy deducts the cost of the marble from the sale price, then multiplies it by the number of marbles listed at that row. The SUMX function then proceeds to sum all of the different function results that were calculated for each row based on the current row context.
The Measure does not create a new column in Timmy`s table and does not occupy space, and only gets calculated when you add it to a created visualization, but more on that later.
Timmy created 2 visuals:
- One with the customer column and the profit_calculated_column, to find out who is his biggest customer.
- The other with the order_id, customer columns and the profit_calculated_column, to find out who is the owner of his biggest order.
The values in the visual get broken down either by the customer or (order_id and customer) due to Power BI Filter Context for more information on that check:
Solution B: Using Calculated Columns
As stated in the notes, Calculated Columns have built-in Row Context, so the answer is pretty straightforward:
The Calculated Column will be stored in Timmy`s table and will increase the data model size but more on that later.
Timmy solved his problem using 2 different methods and made sure that:
- Max was his biggest customer, spending a total of 73.80$.
- Sarah had the biggest standalone order standing at 16.40$
Timmy took a day off from his extensive marble research to write a heartwarming thank you letter to each of Max and Sarah to show his appreciation and gratitude for their help.
Interpretation in Pandas Python:
import pandas as pd
pandas_dataframe = pd.read_csv("timmy_marble_orders.csv")
# Create a new profit column that calculate the profit of each row
pandas_dataframe["profits"] = (pandas_dataframe["sale_price"]-pandas_dataframe["cost"])*pandas_dataframe["count"]
'''
Group By using the customer only and aggregate the result based on the sum of
the profits column then sort the result by the profits column in descending
order.
'''
profits_by_customer = pandas_dataframe.groupby(by="customer").agg({"profits":"sum"}).sort_values(by="profits", ascending=False)
print(profits_by_customer)
'''
Group By using the order_id and customer and aggregate the result based on the
sum of the profits column then sort the result by the profits column in
descending order.
'''
profits_by_order_id_and_customer = pandas_dataframe.groupby(by=["id","customer"]).agg({"profits":"sum"}).sort_values(by="profits", ascending=False)
print(profits_by_order_id_and_customer)Interpretation in SQL:
--Biggest Customer
-- select the customer name and calculated profit then group it by the customer
-- and order it by the customer profit in descending order
select customer,sum((sale_price-cost)*counter) as "customer_profit" from timmy_marble_orders group by customer order by customer_profit desc;
--Biggest Order
-- select the order_id, customer name and calculated profit then group it by
-- the order_id and customer and order it by the order profit
-- in descending order
select order_id,customer,sum((sale_price-cost)*counter) as "order_profit" from timmy_marble_orders group by order_id, customer order by order_profit desc;