Forum Discussion
Average Invoice Value
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.
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.
Ahh - ok. Maybe try:
Average Invoice Value = DIVIDE ( SUM( 'Table Name'[Sum of Price]), DISTINCTCOUNT('Table Name'[Sales Invoice Number] ))
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.:smileyhappy:
Regards
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
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.:smileywink:
Regards
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.
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.:smileyhappy:
Regards
Data is from a direct query. What is the best way to get you what information you need.
Regards,
Scott
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?:smileyhappy:
Regards
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
