Forum Discussion

Jamey's avatar
Jamey
Icon for Helper I rankHelper I
5 years ago
Solved

How 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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    Jamey 

    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?

     

Jamey's avatar
Jamey
Icon for Helper I rankHelper I
5 years ago

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 DataRaw DataPowerBI ViewPowerBI View
INVOICE_ITEMGROSS_SALESINVOICE_ITEMGROSS_SALES
0012262201 129121226220112912
0012262301 9370122623019370
0012262401 159601226240115960
0012262501 6890122625016890
0012262502 6870122625026870
0012262601 9426122626019426
0012262602 5194.29122626025194.29
0012262603 7092122626037092
0012262701 1304.5122627011304.5
0012262702 2048.09122627022048.09
0012262801 13973.41226280113973.4
0012262901 10938.231226290110938.23
0012262902 8737.24122629028737.24
0012263001 2084.2122630012084.2
0012263101 22411.621226310122411.62
0012263101 22411.62  
0012263201 7976122632017976
0012263202 26001.761226320226001.76
0012263203 1262122632031262
0012263301 4454.8122633014454.8
0012263401 5187122634015187
0012263402 4754.75122634024754.75
0012263403 11983.581226340311983.58
0012263403 11983.58  
0012263501 22554.361226350122554.36
0012263601 24116.41226360124116.4
0012263701 15827.941226370115827.94
0012263702 4595.88122637024595.88
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

 

  • Jamey's avatar
    Jamey
    Icon for Helper I rankHelper I
    5 years ago

    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!