Calculating average of products sold

[Anonymous]

Hi

I'm pretty sure there's some very basic error i'm doing.

My company sells cars. Each car sold has a price, based on its model.

So I have a table SALES that has one row for each sales, with a salesid number and a reference to the MODEL table. So there's a relationship (bidirectional) between SALES and MODEL.

Now, I can sell many times the same model so if I choose a certain date range i can have something like

SALESID | MODELID | PRICE

  123  | 789 | 10.000

  124  | 342 | 15.000

  125  | 789 | 10.000

So as you can see several sales can reference the same MODEL.

Now if put this data in a table and calculate the average of PRICE the number is not correct. My feeling (I have thousands of rows so not easy to debug) is that PowerBi is calculating (10.000+15.000)/2 instead of (10.000+15.000+10.000)/3 because it sees 2 unique rows in MODEL table.

I'm a novice and I'm pretty sure that this is basic mistake, can you help me? Thanks

[gipiluso]

if the expected result is (10.000+15.000+10.000)/3 powerbi should work well.

have a look at the pbix attached

[Anonymous]

Then there's something very weird. See image, if you calculate the average values of the last column, it's 13.565. But Powerbi shows 12.766. Where's my mistake?

[Anonymous]

I see what happens. PowerBI takes one value for each id_vehicle and count them only once, then calculates the average.
So it's (11900+17550+11850+11900+11050+12350)/6 and makes 12.766

It's an expected behaviour?

[gipiluso]

it is not expected.

As per my example should calculate the average of each single value (see this pbix).

It should not be related but in any case I will remove the possibility to summurize by id column (ie: set do not summarize on id_vehicle_historical )

[Anonymous]

Thanks gipiluso, did what you suggested but didn't help

So, just to recap

- there is a SALES table with a bidirectional join with a PRODUCT table (there are also COUNTRY and DATE dims)

- each sale has a product key to the PRODUCT table, with a one-to-many relationship

- I choose a date period and a country and a subset of SALES is selected (and you can see that the column "lead id" is different)

- a unique row of the PRODUCT table (i.e. vehicle) is selected with his price and the average of the prices is not the correct one.

What is wrong in here?

[Anonymous]

by the way i've downloaded you pbix and it's made of one single table. If I create a two table model from your data, it does have the same "problem".

There must be a serious conceptual mistake on my side

[Anonymous · Accepted solution]

After reading a few docs online I think that the main mistake is that I've put a fact value in a dimension (price should be in the SALES table rather than in the PRODUCT one). I'll rewrite my model moving data in there.

If I get it right, what powerbi does is to select each product that comes from the join (regardless of how many times it has been sold) and averages that.

Noob mistake 🙂