Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Thank you so much !
I'm using this code + I changed one relation from both to single and now it's working perfectly !
Hi @Anonymous
You should check your relationships I guess
Hi, @Anonymous , thanks for the answer !
The Stock Table is linked with the Product List and the Sales History through the Code column
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |