Accurately View Price Reports with Multiple Vendors and Multiple Materials

  • by Andrey Bondarev, Senior BI Consultant, Bluefin Solutions Ltd.
  • October 1, 2005
The sum of prices per product often is not an accurate measure of performance when comparing multiple vendors, materials, and prices. The weighted average method, however, adjusts the sums proportionately so that you can make an accurate judgment of vendor performance. While BW has no standard weighted average method of aggregation, you can model it in BEx Query Designer using calculated key figures.
Key Concept
The weighted average aggregation method makes quantities consistent at any drill-down level, without major changes to the back end, by using sums adjusted to reflect the proportionate prices of materials sold by different vendors.

In my work with clients, I have encountered several functional and technical issues involving product pricing. Aggregating the prices for comparison purposes is not as straightforward as simply adding the prices or taking the average of the prices because these figures are not consistent across drill-down levels. However, by weighting the averages, I can create a report that reflects the proportionate prices of each vendor across drill-down levels. This allows you to compare the purchasing price variance (PPV) to determine which vendor gives you the best value. I will show you how to use BEx Query Designer to model a weighted average aggregation method using calculated key figures.

Say you purchase three materials from two different vendors and you want to compare the performance of each vendor in July 2005 by using the purchasing performance report. Multiple vendors, materials, and prices are involved, so neither summation methods nor average methods present a consistent, accurate view of price reporting. Using the weighted average aggregation method lets you view price reporting in this situation and reflects all prices proportionately depending on the quantity sold in a given time frame.

Even if your reporting requirements on prices are not as complex as described in this article, you may find some of my tips and tricks useful. Purchasing is just one of the examples of the application of the weighted average aggregation method. You could apply the same methods described in this article to a Sales and Distribution (SD) report. For example, instead of vendors, you could use this method for customers. This functionality has proven to work with BW 3.x.

I’ll explain the theory behind the weighted average aggregation method and show you how it provides consistent reporting at all drill-down levels. Using my example of a purchase performance report, I’ll walk you through the thought process of setting up the necessary formulas, show you how to implement them in BW, and demonstrate how the weighted average aggregation method works for both drill-down by material and drill-down by vendor.

Andrey Bondarev

Andrey Bondarev is a senior BI consultant for Bluefin Solutions, a UK-based SAP partner. Andrey has more than five years of experience in many areas of SAP BW, specializing in data modeling and performance optimization.

See more by this author


Comments

No comments have been submitted on this article. 


Please log in to post a comment.

To learn more about subscription access to premium content, click here.