Forum Discussion
Average Invoice Value
"I dont think I can add a calcuation at the report level?"
I don't understand what you are saying here. You only want at a grand total line? How are the other two columns calculated? As measures or calculated columns?
Matt,
On the report I want to add a column to the right that has the average invoice value for each customer. So as an example complete pool and spa part supply would have the value of 134.80 divided by the 3 invoices that created that gross Revenue. In an excel summary I would just add that column to the sheet. I would think you could add a column in the visualization table where that value could be calculated.
I think that mattbrice was suggesting that you add a new measure to do the calculation, then add that measure to the table.
For example:
1. Click Modeling > New Measure
2. Type the formula, e.g:
Average Invoice Value = DIVIDE ( [Sum of Price], [Count of Sales Invoice Number] )
3. Add that measure to the Values of your table
Not a programmer so maybe syntax wrong. This is what I got from the formula.
Apologies, the formula I used would only work if those fields were already measures...but they aren't.
A quick fix is to use this formula:
Average Invoice Value = DIVIDE ( SUM( 'Table Name'[Sum of Price]), SUM('Table Name'[Count of Sales Invoice Number] ))
(where 'Table Name' is the name of the table that stores those fields - I can't read it all in your screenshot, but it starts with 'Daily Item Sales by...' )
In the longer term, its usually a good idea to create 'explicit' measures for things like 'Sum of Price', then use those measures in other formulas. That helps if you ever need to change the measure, because you only have to edit it in one place.
