Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a sales table with the following information:
Date | Product | Total Sales | Units sold | Price per unit |
1-1-2019 | A | 100 | 10 | 10 |
1-2-2019 | A | 200 | 10 | 20 |
1-3-2019 | B | 300 | 10 | 30 |
1-1-2019 | B | 100 | 10 | 10 |
1-2-2019 | B | 200 | 10 | 20 |
1-3-2019 | C | 300 | 10 | 30 |
1-1-2020 | C | 100 | 10 | 10 |
1-2-2019 | C | 200 | 10 | 20 |
1-3-2020 | D | 300 | 10 | 30 |
1-1-2019 | D | 100 | 10 | 10 |
1-1-2020 | D | 200 | 10 | 20 |
1-2-2019 | E | 100 | 10 | 10 |
1-3-2020 | E | 200 | 10 | 20 |
1-1-2020 | E | 300 | 10 | 30 |
And I have a product table that gives me more detailed information about each product. The two tables are linked.
Product | Product Category |
A | AAAA |
B | AAAA |
C | AAAA |
D | BBBB |
E | BBBB |
I also have a separate date table that is linked to the sales date from the sales table.
As you can see, the price per unit data differs by product, and each product can also have a different price per unit on any given day or any different year.
I would like to assign a price tier to each product, based on these three rules:
If you do the maths based on the examples, the price tier should show up as below. I’ve included ‘product category’, ‘year’, ‘total sales’, ‘total units sold’ and ‘average price per unit for this category and year’ columns for this example and marked them in blue. Ideally I’d have one calculation that does not requires any additional columns apart from the ‘price tier’ calculated column. This sales table contains millions of rows of data so the less columns the better!
Date | Product | Total Sales | Units sold | Price per unit | Product category | Year | Total sales | Total units sold | Average price per unit for this category and year | Price Tier |
01-01-19 | A | 100 | 10 | 10 | AAAA | 2019 | 1500 | 70 | 21 | cheap |
01-02-19 | A | 200 | 10 | 20 | AAAA | 2019 | 1500 | 70 | 21 | normal |
01-03-19 | B | 300 | 10 | 30 | AAAA | 2019 | 1500 | 70 | 21 | luxury |
01-01-19 | B | 100 | 10 | 10 | AAAA | 2019 | 1500 | 70 | 21 | cheap |
01-02-19 | B | 200 | 10 | 20 | AAAA | 2019 | 1500 | 70 | 21 | normal |
01-03-19 | C | 400 | 10 | 40 | AAAA | 2019 | 1500 | 70 | 21 | luxury |
01-01-20 | C | 100 | 10 | 10 | AAAA | 2020 | 100 | 10 | 10 | normal |
01-02-19 | C | 200 | 10 | 20 | AAAA | 2019 | 1500 | 70 | 21 | normal |
01-03-20 | D | 300 | 10 | 30 | BBBB | 2020 | 1000 | 40 | 25 | luxury |
01-01-19 | D | 100 | 10 | 10 | BBBB | 2019 | 200 | 20 | 10 | normal |
01-01-20 | D | 200 | 10 | 20 | BBBB | 2020 | 1000 | 40 | 25 | luxury |
01-02-19 | E | 100 | 10 | 10 | BBBB | 2019 | 200 | 20 | 10 | normal |
01-03-20 | E | 200 | 10 | 20 | BBBB | 2020 | 1000 | 40 | 25 | cheap |
01-01-20 | E | 300 | 10 | 30 | BBBB | 2020 | 1000 | 40 | 25 | cheap |
What would be the best approach to land on this calculated column?
Best regards
Bas
Solved! Go to Solution.
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description, I think you can create some measures to calculate the correct result.
Like this:
Price Tiger =
VAR a =
SUMX (
FILTER (
ALL ( 'Table1' ),
RELATED ( Table2[Product Category] ) = [category]
&& YEAR ( Table1[Date] ) = YEAR ( SELECTEDVALUE ( 'Table1'[Date] ) )
),
Table1[Total Sales]
)
VAR b =
SUMX (
FILTER (
ALL ( 'Table1' ),
RELATED ( Table2[Product Category] ) = [category]
&& YEAR ( Table1[Date] ) = YEAR ( SELECTEDVALUE ( 'Table1'[Date] ) )
),
Table1[Units sold]
)
VAR c =
DIVIDE ( a, b )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( Table1[Price per unit] ) > c * 1.1, "luxury",
SELECTEDVALUE ( Table1[Price per unit] ) = c * 1.1, "normal",
SELECTEDVALUE ( Table1[Price per unit] ) < c * 1.1, "cheap"
)
Here is my sample .pbix file. Help this helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description, I think you can create some measures to calculate the correct result.
Like this:
Price Tiger =
VAR a =
SUMX (
FILTER (
ALL ( 'Table1' ),
RELATED ( Table2[Product Category] ) = [category]
&& YEAR ( Table1[Date] ) = YEAR ( SELECTEDVALUE ( 'Table1'[Date] ) )
),
Table1[Total Sales]
)
VAR b =
SUMX (
FILTER (
ALL ( 'Table1' ),
RELATED ( Table2[Product Category] ) = [category]
&& YEAR ( Table1[Date] ) = YEAR ( SELECTEDVALUE ( 'Table1'[Date] ) )
),
Table1[Units sold]
)
VAR c =
DIVIDE ( a, b )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( Table1[Price per unit] ) > c * 1.1, "luxury",
SELECTEDVALUE ( Table1[Price per unit] ) = c * 1.1, "normal",
SELECTEDVALUE ( Table1[Price per unit] ) < c * 1.1, "cheap"
)
Here is my sample .pbix file. Help this helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-janeyg-msft ,
Thank you very much, this is amazing. The measure works perfect when I import it into my model.
However, it does need to be a calculated column in Table1. This is because I need to use the result as a filter, plus I need to use it as a legend in charts.
When I copy the measure and simply add it as a calculated colum, all price tiers show up as 'normal'. What needs to be changed for this to work as a calculated column?
Best regards
Bas
Hi, @Anonymous
Try this:
Price Tiger1 =
VAR x =
CALCULATE ( MAX ( Table2[Product Category] ) )
VAR a =
SUMX (
FILTER (
ALL ( 'Table1' ),
RELATED ( Table2[Product Category] ) = x
&& YEAR ( Table1[Date] ) = YEAR ( 'Table1'[Date] )
),
Table1[Total Sales]
)
VAR b =
SUMX (
FILTER (
ALL ( 'Table1' ),
RELATED ( Table2[Product Category] ) = x
&& YEAR ( Table1[Date] ) = YEAR ( 'Table1'[Date] )
),
Table1[Units sold]
)
VAR c =
DIVIDE ( a, b )
RETURN
SWITCH (
TRUE (),
Table1[Price per unit] > c * 1.1, "luxury",
Table1[Price per unit] = c * 1.1, "normal",
Table1[Price per unit] < c * 1.1, "cheap"
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft ,
Thanks for this. I've tried to excute the calculation but PowerBi keeps stuck at it's 'loading' screen. Perhaps SUMX is too much for 5 million rows?
I've therefore tried executing things step by step, starting with only looking at the YEAR, and only using variable A.
VAR a =
SUMX (
FILTER (
ALL ( 'Table1' ),
YEAR ( Table1[Date] ) = YEAR ( 'Table1'[Date] )
),
Table1[Total Sales]
)
RETURN
a
It calculates when I execute this, but no matter which year is in the date column, it always returns the same value (the total amount of sales). What would be the solution to fix this? Perhaps I can replicate that to the other variables to get it to work!
Thanks again
Bas
Hi, @Anonymous
If use measure,try this:
YEAR ( Table1[Date] ) = YEAR ( selectedvalue('Table1'[Date] ))
If use column,try this:
YEAR ( Table1[Date] ) = YEAR ( earlier('Table1'[Date] ))
If it doesn’t solve your problem, please feel free to ask me.
You should know that using calculated columns is very memory intensive,when your data volume is huge,I suggest you use measure or reduce data.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-janeyg-msft Thanks again. That works.
I've tried amending the rest of the calculation. But unfortunately I keep getting the message that 'there's not enough memory to complete this operation.
I think the calculation works though. It's just that PowerBI cannot handle the SUMX function unfortunately...
best regards
Bas
Hi, @Anonymous
Try this: Create a,b,c in the form of measure and create final switch function in the form of column.May be it helps.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |