Forum Discussion
Jamey
Helper I
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 ...
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?
JameyHelper I
Helper IThe 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 |
jdbuchanan71
Super User
Super UserSorry, 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?