Understanding Totalling of Measure

[stuartp22]

Hi,

I'm new to Power BI and DAX and have made a newbie mistake in totalling a measure on a report I was creating.  I've fixed it, but still don't understand why it needs to be this way... hoping someone can help explain it to me.

The basic requirement is to report on product sales - the average quantity sold per month over 6 months, and what the cost would be to purchase 1 month of stock at the current average cost.

My data model is as follows:

To explain the tables:

K8_OS_01_BUFFR is where all sales transactions are held, line by line.

K8_BR_01_PRODF is the product file where cost is held.

K8_CF_01_CUSTF is customers (not relevant for this question)

To begin with I set up a table of product, sum of quantity, monthly average sales and average cost.  My monthly average sales is a measure on K8_OS_01_BUFFR: 

Monthly Average = SUM(K8_OS_01_BUFFR[QUANTITY]) / 6

I then created another measure, Purchase Cost 1 Month, like so: 

Purchase Cost 1 month = [Monthly Average] * AVERAGE(K8_OS_01_BUFFR[Average Cost])

This gives me the correct purchase cost on a line by line basis, but an incorrect total as you will see below (simplified to two products only, product description redacted for commercial confidentiality reasons) 

 After much searching and following this thread https://community.powerbi.com/t5/Desktop/Sum-of-a-calculated-measure-column/td-p/446458 I have discovered that I need to use SUMX and set up my measure like this 

CORRECT Purchase Cost 1 Month = SUMX(K8_OS_01_BUFFR,(K8_OS_01_BUFFR[QUANTITY]/6) * K8_OS_01_BUFFR[Average Cost])

However I also thought that I should be able to use my Monthly Average measure here and set it up like this 

Purch Cost with Measure = SUMX(K8_OS_01_BUFFR,[Monthly Average] * K8_OS_01_BUFFR[Average Cost])

 but it doesn't work:

Please can someone help me understand the following:

1. Why did I need to create an "average cost" column in my Buffer table to calculate my Purchase Cost 1 Month measure?  I brought it in as a new RELATED column becuase I couldn't see any way to reference K8_BR_01_PRODF[AVGCOST] in my measure.  Is there a better way to do this?
2. Why does my Purchase Cost 1 Month column not sum as I expected?  I thought it might be doing Monthly Average * Average of Average Cost but even this doesn't seem to be the case.
3. Why can I not use my Monthly Average measure in the final column, instead having to stipulate the actual calculation in SUMX to get the correct result?

Thanks 🙂

[lbendlin]

A sum of averages is different from an average of sums.

First step is to be clear on what you actually want to compute

Second step is to implement that well defined (and confirmed with your business users) logic in DAX.