New calculated column for Purchase Rate comparison

[Anonymous]

Dear Experts,

I have a table of purchase rates which have transactional data of.

1- Our internal purchase

2-Data of three competitors

I need  a calcualted column which shows the comparison based on last purchase rate of an item (better rate, lesser rate, similar rate).

The comparison is based on the last rate at which we bought an Item and the last purchase rate of that of the competitor for the same item.

I need to put this new column in a slicer so I realy need a column not a measure. Please see my sample file.

https://www.dropbox.com/sh/ko9y6lo6ocwp6om/AABuUT3RK7G6yBdPCBxmv8G5a?dl=0

Please help. I am struggling with this issue for many days and its very urgent.

Regards,

Imran

[vivran22 · Accepted solution]

Hello @Anonymous ,

Can you help in understanding the outcome for the item below:

Item Code	Item Description	Importer Name	Date	Purchase Rate	Data Type
4499001	Sodium	SAAM PHARMA	30-May-18	97	Competitor Data
4499001	Sodium	SAAM PHARMA	16-May-18	97	Competitor Data
4499001	Sodium	SAAM PHARMA	16-Apr-19	82	Competitor Data
4499001	Sodium	Q PHARMA	22-Apr-19	83	Competitor Data
4499001	Sodium	Q PHARMA	22-Apr-19	83	Competitor Data
4499001	Sodium	Q PHARMA	23-Apr-19	83	Competitor Data
4499001	Sodium	SAAM PHARMA	17-Aug-18	90	Competitor Data
4499001	Sodium	SAAM PHARMA	13-Aug-18	183	Competitor Data
4499001	Sodium	SAAM PHARMA	16-Jul-18	122	Competitor Data
4499001	Sodium	LABORATORIES LIMITED	12-Jul-19	81	Internal Data
4499001	Sodium	LABORATORIES LIMITED	12-Jul-19	182	Internal Data
4499001	Sodium	LABORATORIES LIMITED	10-Oct-19	77	Internal Data
4499001	Sodium	LABORATORIES LIMITED	10-Oct-19	77	Internal Data

It will help in creating solution accordingly.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[vivran22 · Accepted solution]

Hello @Anonymous ,

Can you help in understanding the outcome for the item below:

Item Code	Item Description	Importer Name	Date	Purchase Rate	Data Type
4499001	Sodium	SAAM PHARMA	30-May-18	97	Competitor Data
4499001	Sodium	SAAM PHARMA	16-May-18	97	Competitor Data
4499001	Sodium	SAAM PHARMA	16-Apr-19	82	Competitor Data
4499001	Sodium	Q PHARMA	22-Apr-19	83	Competitor Data
4499001	Sodium	Q PHARMA	22-Apr-19	83	Competitor Data
4499001	Sodium	Q PHARMA	23-Apr-19	83	Competitor Data
4499001	Sodium	SAAM PHARMA	17-Aug-18	90	Competitor Data
4499001	Sodium	SAAM PHARMA	13-Aug-18	183	Competitor Data
4499001	Sodium	SAAM PHARMA	16-Jul-18	122	Competitor Data
4499001	Sodium	LABORATORIES LIMITED	12-Jul-19	81	Internal Data
4499001	Sodium	LABORATORIES LIMITED	12-Jul-19	182	Internal Data
4499001	Sodium	LABORATORIES LIMITED	10-Oct-19	77	Internal Data
4499001	Sodium	LABORATORIES LIMITED	10-Oct-19	77	Internal Data

It will help in creating solution accordingly.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[Anonymous]

Take the example of SAAM Pharma whose last purchase rate of Sodium is 90 (on 17the Aug, the date when the last purchase was made.) whereas our last rate is 77 (10-Oct-19). So in this case our purchase rate is better than that of SAAM Pharma.

Actually The analysis is based on three parameters.

1. Competitors

2. Buying similar materials

3. on different prices

So I need to have three slicers in my dashboard. I can easily insert competitor and item slicer because they are there in my data, what I need is a caculated column for purchase rate comparison so that I can put it in slicer.

Regards.

[v-lili6-msft · Accepted solution]

hi   @Anonymous 

For your case, add two  columns by thi logic:

lastpurchaserate = 
var _itemcode='Sample'[Item Code]
var _importername='Sample'[Importer Name]
var _datatype='Sample'[Data Type]
return
var _lastdate=IF('Sample'[Data Type]="Internal Data",CALCULATE(MAX('Sample'[Date]), FILTER('Sample','Sample'[Item Code]=_itemcode &&'Sample'[Data Type]="Internal Data")),CALCULATE(MAX('Sample'[Date]), FILTER('Sample','Sample'[Item Code]=_itemcode &&'Sample'[Importer Name]=_importername))) return
var _lastpurchaserate= IF('Sample'[Data Type]="Internal Data",CALCULATE(MAX('Sample'[Purchase Rate]), FILTER('Sample','Sample'[Item Code]=_itemcode &&'Sample'[Date]=_lastdate)),CALCULATE(MAX('Sample'[Purchase Rate]), FILTER('Sample','Sample'[Item Code]=_itemcode &&'Sample'[Importer Name]=_importername&&'Sample'[Date]=_lastdate)))
 return
_lastpurchaserate

Flag = 
var _itemcode='Sample'[Item Code] return
var _minlastpurchaserate=CALCULATE(MIN('Sample'[lastpurchaserate]),FILTER('Sample','Sample'[Item Code]=_itemcode)) return
IF(CALCULATE(MAX('Sample'[Data Type]),FILTER('Sample','Sample'[Item Code]=_itemcode&&'Sample'[Purchase Rate]=_minlastpurchaserate))="Internal Data","our purchase rate",CALCULATE(MAX('Sample'[Importer Name]),FILTER('Sample','Sample'[Item Code]=_itemcode&&'Sample'[Purchase Rate]=_minlastpurchaserate)))

And here is sample pbix file, please try it.

Regards,

Lin