Aggregate multiplication

[joslaz]

Hey community!
I would like to add a measure to determine the revenue per department.
I have an ItemMaster that contains per Article the department and cost information.
Then I have a sales table that contains all sales per item per day.

Now I have a measure like [Sales].
How can I define a measure, that shows me the revenue per Order and per Department?
This measure works perfectly on order level:

[Sales]*AVERAGE(ItemMaster[Cost])

But If I aggregate this measure, then I get a false value.
Do I have to use SUMX? Or any other approaches?

 

[JLGWhiz]

My ESP license expired, I need to see a copy of your worksheet so I will know what your references mean in the formulas you post. Use tje xl2BB on the tool bar feature for efficiency.

 

[macfuller]

Is an item available to more than one department, and at more than one cost? For example, widget 123 may be available to department 331 at a unit cost of $3.25, but is it also available at a unit cost of $3.50 to department 552? If you have a relationship between your item table and the sales table you can use the RELATED() function to pull the sale price, assuming that your sales table only has quantity and not price. Depending on how large your sales table is, you may want to have a calculated column with the price rather than using a measure to calculate it. Takes a bit longer to load but the measure will return faster.

Without further data I can only offer a general solution. Depending on how many measures you will need (do you want total revenue by itself for example?) you can create this in parts or a whole. Your original measure works for Order only because there's something in your data where the cost happens to align with the order data.

Let's say you've created a calculated column [Revenue]=RELATED ( ItemMaster[Cost] ) * Sales[Quantity]. If the tables aren't related you'll need some variant of LOOKUPVALUE instead.

For a single solution you can then have a simple [Average Revenue]:=AVERAGE( Sales[Revenue] ). This should be valid if your pivot table is grouping by either order or department.

For individual measures you can create a [Total Sales] := SUM ( Sales[Revenue] ) measure and you're halfway home. Add a [Number of Sales] := COUNTROWS ( Sales ) measure, then you can have your [Average Revenue]:=DIVIDE ( [Total Sales], [Number of Sales] )

There are ways to do this without having the tables related, but as JLGWhiz noted you'll need to post your worksheet and an explanation of your model.