Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New calculated column for Purchase Rate comparison

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 bas...
Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    Hello Anonymous ,

     

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

     

    Item CodeItem DescriptionImporter NameDatePurchase RateData Type
    4499001SodiumSAAM PHARMA30-May-18                               97Competitor Data
    4499001SodiumSAAM PHARMA16-May-18                               97Competitor Data
    4499001SodiumSAAM PHARMA16-Apr-19                               82Competitor Data
    4499001SodiumQ PHARMA22-Apr-19                               83Competitor Data
    4499001SodiumQ PHARMA22-Apr-19                               83Competitor Data
    4499001SodiumQ PHARMA23-Apr-19                               83Competitor Data
    4499001SodiumSAAM PHARMA17-Aug-18                               90Competitor Data
    4499001SodiumSAAM PHARMA13-Aug-18                             183Competitor Data
    4499001SodiumSAAM PHARMA16-Jul-18                             122Competitor Data
    4499001SodiumLABORATORIES LIMITED12-Jul-19                               81Internal Data
    4499001SodiumLABORATORIES LIMITED12-Jul-19                             182Internal Data
    4499001SodiumLABORATORIES LIMITED10-Oct-19                               77Internal Data
    4499001SodiumLABORATORIES LIMITED10-Oct-19                               77

    Internal Data

     

     

    It will help in creating solution accordingly.

     

    Cheers!
    Vivek

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

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

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