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 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

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

3 Replies

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    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

    • 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