Average Invoice Value

[sbmeder]

Have a table that displays customer name, customers gross sale values for a filtered time period and the number of invoices relating to those sales. Want to add another column in the report that shows average invoice value per customer. 

Regards,

Scott

[mattbrice]

Can you just divide the gross sales value measure by the number of invoice measure you already have?

[sbmeder]

Matt,

Dont think so. Using the BI functions to key off customer name, aggregate invoice value into total value, then using the count distinct function to determine qty of invoices. So I have all the pieces of the puzzle, but not familiar enough with the BI product. I dont think I can add a calcuation at the report level?

Scott

[mattbrice]

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

[sbmeder]

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.

[MalS]

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

[sbmeder]

Not a programmer so maybe syntax wrong. This is what I got from the formula.

[MalS]

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.

[sbmeder]

Almost there. The invoice count is not a measure. It is created by using the count distinct feature  of invoice numbers that relate to the customer in column 1. So  right now getting $0 in that column.

[v-ljerr-msft]

Hi @sbmeder,

Have you tried the formula provided by MalS above? It should work in your scenario. If you still have any question, feel free to ask.

Regards

[sbmeder]

Unfortunately, I have not had a chance.  I have finally gotten my owner's attention with this product and what it can do for the Company. What is the best way to find consultants to help structure reports so we can slice and dice the data.  My main issue is dealing with the date functionality. I am not a programmer, and I know BI will do what I want it to do, but I have no idea how to write DAX.  looking at customer sales, last month vs this month. Last year vs This year. Same thing with Product lines and Geographic data. 

Any help would be appreciated. 

Regards,

Scott

[v-ljerr-msft]

Hi @sbmeder,

That's all right! We have a lot DAX experts in the forum. If you have any issue with DAX, just create a new thread, and post your table structures with some sample data and your expected result. I'm sure you will get the issue resolved.

Regards

[MalS]

Ahh - ok. Maybe try:

Average Invoice Value = DIVIDE ( SUM( 'Table Name'[Sum of Price]), DISTINCTCOUNT('Table Name'[Sales Invoice Number] ))

[sbmeder]

Mal,

Finally had a chance to use your suggestion, still not getting the correct calculation.  For Leslie's the average invoice value should be 22.34. 

[v-ljerr-msft]

Hi @sbmeder,

A little weird. The formula seems all right.

Could you share a sample pbix file which can reproduce the issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[sbmeder]

Data is from a direct query.  What is the best way to get you what information you need.

Regards,

Scott

[v-ljerr-msft]

Hi @sbmeder,

Is it possible for you to create a new sample pbix file to import the table from your data source, then share it with us?

Regards

[sbmeder]

I have the provided sample data.  I select customer number, then data field " Sum of Price" bad name for a data field and let BI summarize the values for all transactions for each customer number.  Then I select Invoice number and ask for distinct count to get the number of invoices. Hope this helps explain the situation. 

https://ebsbi.sharefile.com/d-sae5c5584a564dc8b

Regards,

Scott

[Vvelarde · Accepted solution]

@sbmeder

You can use the same Dax Formula like a measure instead of a calculated Column