Forum Discussion
Helper IHow to SUM an AVERAGE Column
I have a table that can contain multiple entries with header information for the same Invoice - see example:
Customer | Invoice No | Sales | Production Order | Cost Qty
XYZ Inc 12345601 $10,000 123 4000
XYZ Inc 12345601 $10,000 456 2000
When I put this into a Table View in Power BI it properly shows one row for XYZ Inc Invoice 12345601 BUT the Sales will show as $20,000 instead of $10,000. So, I made that column an Average instead of a Sum so it shows the proper $ amount. However, now my total at the bottom is incorrect. I've tried a couple DAX things but the total still doesn't show correctly. Can you please help me with this senerio?
Sorry, I missed a context transition in my reply, try it like this.
SUMX Average CALCULATE = SUMX ( VALUES ( 'Table'[INVOICE_ITEM] ), CALCULATE ( AVERAGE ( 'Table'[GROSS_SALES] ) ) )I believe that is the value you are looking for yes?
9 Replies
- jdbuchanan71
Super User
Give something like this a try.
Avg Sales = SUMX ( VALUES ( YourTable[Invoice No] ), AVERAGE ( YourTable[Sales] ) )
JameyI've tried something like that already and got the same result but it's not the right amount for some reason. If I export the table to Excel and total it, it's not the same as the answer (lower) but it is closer than the orignal amout that was doubling, tripling, etc.. some items.
- Jihwan_Kim
Hi, Jamey
I am not sure about how your data model and entire table look like, but please try the below.
Sales Fix =
VAR currentinvoicenumber =
MAX ( 'Table'[Invoice No] )
VAR rowscount =
CALCULATE (
COUNT ( 'Table'[Invoice No] ),
'Table'[Invoice No] = currentinvoicenumber
)
RETURN
DIVIDE ( SUM ( 'Table'[Sales] ), rowscount )Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
- Jamey
The sample data below represents my Raw Data on the Left and how it appears in a Power BI Table view on the Right with the Gross Sales column as an Average so it does not inflat the gross sales rows that are multiples. I added the row spaces for ease of reading. My goal is to sum the Average column but it does not calculate correctly. Any help with this would be greatly appreciated - thank you!
Raw Data Raw Data PowerBI View PowerBI View INVOICE_ITEM GROSS_SALES INVOICE_ITEM GROSS_SALES 0012262201 12912 12262201 12912 0012262301 9370 12262301 9370 0012262401 15960 12262401 15960 0012262501 6890 12262501 6890 0012262502 6870 12262502 6870 0012262601 9426 12262601 9426 0012262602 5194.29 12262602 5194.29 0012262603 7092 12262603 7092 0012262701 1304.5 12262701 1304.5 0012262702 2048.09 12262702 2048.09 0012262801 13973.4 12262801 13973.4 0012262901 10938.23 12262901 10938.23 0012262902 8737.24 12262902 8737.24 0012263001 2084.2 12263001 2084.2 0012263101 22411.62 12263101 22411.62 0012263101 22411.62 0012263201 7976 12263201 7976 0012263202 26001.76 12263202 26001.76 0012263203 1262 12263203 1262 0012263301 4454.8 12263301 4454.8 0012263401 5187 12263401 5187 0012263402 4754.75 12263402 4754.75 0012263403 11983.58 12263403 11983.58 0012263403 11983.58 0012263501 22554.36 12263501 22554.36 0012263601 24116.4 12263601 24116.4 0012263701 15827.94 12263701 15827.94 0012263702 4595.88 12263702 4595.88 - Jihwan_Kim
Hi, Jamey
I am still quite not sure whether I understood your question correctly, but please check the below picture and the sample pbix file's link down below.
My previous measure (Sales Fix) did not work, however, Sales Fix V2 is working.... I think.. Please check.
Sales Fix V2 =SUMX( VALUES('RawData'[INVOICE_ITEM]), [Sales Fix])Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
- Jamey
Thank you Jihwan Kim!
This solution works but because it's a two step solution by using another Measure, I Accepted the other solution - thanks again!
- jdbuchanan71
Sorry, I missed a context transition in my reply, try it like this.
SUMX Average CALCULATE = SUMX ( VALUES ( 'Table'[INVOICE_ITEM] ), CALCULATE ( AVERAGE ( 'Table'[GROSS_SALES] ) ) )I believe that is the value you are looking for yes?
