Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |