Create a Price Recomendation based on the Average Price by Category and Brand for all products

[Anonymous]

Hi,

I need some help to solve this problem.

I'm trying to create a measure to recomend a price based on the price average for all the products with the same Category and Brand.
My Goal : Matrix Visual with the product's Code, Stock and Price Recomendation.

Sample Data :

Stock = 

Code	Stock
AB123	20
CD456	100
WZ789	15
GH987	46

Product List = 

Code	Category	Brand
AB123	Soda	Mark
XY321	Soda	Penn
CD456	Water	Doctors
EF654	Water	Doctors
WZ789	Juice	Phoe
GH987	Juice	Phoe

Sales History = 

Code	Category	Brand	Price	Date	Retailer
AB123	Soda	Mark	1	21/06/2019	ABC
XY321	Soda	Penn	1,5	21/06/2019	ABC
AB123	Soda	Mark	1,2	14/08/2020	Gen
CD456	Water	Doctors	2	24/03/2020	Gen
EF654	Water	Doctors	1,8	13/08/2020	ABC
EF654	Water	Doctors	1,7	10/08/2020	Gen
WZ789	Juice	Phoe	3	04/03/2021	Gen
WZ789	Juice	Phoe	2,8	29/10/2020	Gen

I was able to solve it by creating a new Table + calculated Column, but not by using a measure.
I would like to solve this with a measure, as it would allow the user to filter the historical data with a Slicer, choosing the period and retailer desired.
But, in my solution the user would have to change the table code.

My solution :

Solution Table = 

VAR YearFilter = 2015

return 
FILTER(
    SUMMARIZECOLUMNS(
        'Product List'[Category], 
        'Product List'[Brand], 
        "Average Price", 
        CALCULATE(
            AVERAGE('Sales History'[Price]),
            YEAR('Sales History'[Date]) >= YearFilter
        )
    ), 
    'Product List'[Category]<>BLANK() &&
    'Product List'[Category]<>"N/A"
)

Price Recomendation Column = 

VAR Price =
CALCULATE(
    AVERAGE('Solution Table'[Average Price]),
    FILTER('Solution Table',
    'Solution Table'[Category] = RELATED('Product List'[Category]) && 
    'Solution Table'[Brand] = RELATED('Product List'[Brand])
    )
)

RETURN
Price

I added the new column at the Stock table.

I don't know if it's clear enought, but I can give you more information if you need !
My main problem is that when I created a measure the suggested price was zero for the products that are not on my Sales History.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

Does it work, Price Recomendation Column, in you pbix file? It does not work will because of the RELATED().If it works will in you pbix, please your pbix after remove privacy information.

I did some change with the dax formula

Price Recomendation Column =
VAR Price =
    CALCULATE(
        AVERAGE( 'Solution Table'[Average Price] ),
        FILTER(
            'Solution Table',
            'Solution Table'[Category] = EARLIER( 'Product List'[Category] )
                && 'Solution Table'[Brand] = EARLIER( 'Product List'[Brand] )
        )
    )
RETURN
    Price

As for creating a measure to achieve this result, I have used the following formula, can you try if it works?

price recomendation =
VAR YearFilter =
    SELECTEDVALUE( 'Table'[Date].[Year] )
VAR _Solution_Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Product List' ),
            'Product List'[Category],
            'Product List'[Brand],
            "Average Price",
                CALCULATE(
                    AVERAGE( 'Sales History'[Price] ),
                    YEAR( 'Sales History'[Date] ) >= YearFilter
                )
        ),
        'Product List'[Category] <> BLANK()
            && 'Product List'[Category] <> "N/A"
    )
VAR _1 =
    AVERAGEX(
        FILTER(
            _Solution_Table,
            [Category] = SELECTEDVALUE( 'Product List'[Category] )
                && [Brand] = SELECTEDVALUE( 'Product List'[Brand] )
        ),
        [Average Price]
    )
RETURN
    _1

Before this you need to create a Calendar table first used to be a slicer.

I put my pbix file in the attachment you can reference. If some thing wrong please let me know.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much !

I'm using this code + I changed one relation from both to single and now it's working perfectly !

[Anonymous]

Hi @Anonymous 

You should check your relationships I guess

[Anonymous]

Hi, @Anonymous , thanks for the answer !

The Stock Table is linked with the Product List and the Sales History through the Code column