Comparison of purchae rates with that of our competitors

[Anonymous]

Hi everyone,

I have four tables;

1. Our Internal data of purchases of last two years

2. Data of three competitors' purchasese

3. Materials list

4. Two years calendar table

Requirement:

I am preparing a comparison of our purchase rates with that of our competitors and want three slicers of ;

1. Items

2. Competitor name

3. Price comparison

Resulting Visuals:

The impacts of these slicers in two table visuals (please see the attached file)

Please see the sample data and PBIX file and suggest me how to achieve this requirement. Your help is much appreciated.

https://www.dropbox.com/sh/iqg5elc44hewvkw/AADkO_Z4FwVmu0w2L_GoSYfoa?dl=0

Regfards,

Imran

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you can operate as follows.

For ‘Competitor Name’ and ‘Item Description’ slicers, you can apply ‘Importer Name’ in ‘Competitors_Data’ and ‘Item Description’ in ‘Items_Lookup’ to corresponding slicers.

For ‘Price Comparsion’ slicer, you may create a table called ‘Price Comparsion’.

Then you can create a measure as follows:

Prise Status =

var currentprice = SUMX('Competitors_Data',[Qty.] * [Rate])

var t = FILTER('Internal_Data','Internal_Data'[Item Code] in FILTERS('Competitors_Data'[Item Code]) && 'Internal_Data'[Date] in FILTERS('Competitors_Data'[Date]))

var otherprice = SUMX(t,[Qty.]*[Rate])

var pricestatus = IF(currentprice>otherprice,"Better Price",IF(otherprice<currentprice*1.1,"Higher Price","Similar Price"))

return if(pricestatus in FILTERS('Price Comparison'[Value]),1,0)

Result:

If I misunderstand the your thoughts, please show us your  expected output. I am glad to solve the problem for you. Here is my pbix .

Best Regards,

Allan

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

[Anonymous]

Thanks for your reply. Actually, what we want to do is that all the three slicers should filter each other i.e, if I only select similar price, it should filter the other two slicers so that I can select competitor and then the products and same is the case if I select a competitor, it should filter Item and Price comparison slicers automatically.

Moreover, the basis of price comparison is only the unit price (rate column). There is no need to multiply qty. with rate.

We want three dynamic slicers, and then resulting two table visuals so that we can review the purchase rates from every aspect.

Please suggest the solution. If possible, we can get in touch on a phone call so that I may better explain.

Regards,
Imran

[Anonymous]

I would appreciate if anyone can help on doing this.