Forum Discussion
Lookup Price Range for Style
Hi,
I have a data set with categories, Styles and Prices. I need to cluster the prices in Price Ranges on Category level.
I have 5 Price Ranges per category and 12 categories, and 10 countries with local currency. If a Styles is a knit with Price 29,95 Euro I need it to be placed in Knit, Price Range 19,99 - 29,99 Euro.
I am quit sure I need to create a relationship between the two tables, but do not know how to...
Example of Data: (Made up numbers and names)
Report 1:
Month Country Category Style Name RRP Lookup Price Range (Need to find)
08.2018 CHE Knit VMWally knit 19,95 9,99 - 19,99
08.2018 DEU Knit VMSisse knit 29,95 19,99 - 29,99
09.2018 DEU Outerwear VMKubra outerwear 59,95 49,99 - 59,99
.... ... ......... ........... ....... ..........
Report 2:
Country Category Min Max Price Range
DEU Knit 0 9,99 0 - 9,99
DEU Knit 10 24,99 10 - 29,99
.... ..... .... .... .....
CHE Outerwear 0 19,95 19,99
.... ....... ...... ..... .....
So I need it to lookup first the country, then the category and then which Price Range the Price is in.
I hope it makes sense what I am trying to do.
Thank you,
/Kristina
Anonymous,
You may refer to the following DAX that adds a calculated column.
Column = MAXX ( FILTER ( Table2, Table2[Country] = Table1[Country] && Table2[Category] = Table1[Category] && Table2[Min] <= Table1[RRP] && Table2[Max] > Table1[RRP] ), Table2[Price Range] )
5 Replies
Anonymous,
You may refer to the following DAX that adds a calculated column.
Column = MAXX ( FILTER ( Table2, Table2[Country] = Table1[Country] && Table2[Category] = Table1[Category] && Table2[Min] <= Table1[RRP] && Table2[Max] > Table1[RRP] ), Table2[Price Range] )Dear Anonymous,
I'm sorry but the relationship between 2 table, must have at least 1 column have the unique key. In your case, you can't create relationship.
Can you share you .pbix file (with dummy data or delete sensitive data)? Maybe I'll need it for solve by another way.
Thanks & Regards,
ManNVSM.