Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate price tier of products

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 the price is 10% higher or more than the average price in the same product category for the same year, then the product should be identified as a ‘luxury’ product.
  • If the price is 10% lower or less than the average price in the same product category for the same year, then the product should be identified as a ‘cheap’ product.
  • Any other products should be identified as ‘normal’.

 

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

 

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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"
    )

v-janeyg-msft_0-1603101447755.png

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.

View solution in original post

7 REPLIES 7
v-janeyg-msft
Community Support
Community Support

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"
    )

v-janeyg-msft_0-1603101447755.png

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.