Problem trying to sum price per month using related tables

[ariel_skn]

Hi, I was playing around with some tables and I found something weird (at least to me). One of these tables is the sales table which contain all the products sold by date and quantity (important cols are product id - date) and the other is the products table which have the info about products (important cols are product id - price). (sales and products tables are related using product_id column with "many to one" cardinality and "both" cross filter direction)

My problem  --> If I make a visual table using Date (from sales ) and price (from products) as Values, and show only months I get wrong results. It doesn't sum the prices of every product sold in a month like I would expect. I checked it by making an additional column in the sales table named "lookup_price" following this formula:

lookup_price = LOOKUPVALUE(products[price],products[product_id],sales[product_id])

So, I'm sure the lookup_price approach is correct, but I would expect the same behaviour simply by using the raw price column. What's going on here?  (both price and lookup_price are in "sum mode"). In the attached image it's shown what I'm getting:

Any help is appreciated!

PS: I know that strictily I should sum up the [price * quantity] for each row in sales table, but the problem I'm facing doesn't have to do anything with it, it's more like how the relationship between tables and grouping is working.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please also show the result clearly there.

[ariel_skn]

pbix file:

https://drive.google.com/file/d/1flM71e4ZqBBfltEGWAybWG84zEqxd_Y3/view?usp=sharing 

data file:

https://docs.google.com/spreadsheets/d/1EB2WnhmrOqawl9WjTbUDziAua-Bc-wCi/edit?usp=sharing&ouid=10819... 

[Ashish_Mathur]

Hi,

Neither of them is correct.  The correct price should be the weighted average price.  You may download my PBI file from here.

Hope this helps.

[VahidDM]

@ariel_skn 

Measure = 
Var _A = ADDCOLUMNS(sales,"P",RELATED(products[Price]))
return
SUMX(_A,[P])

that measure works fine:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ariel_skn]

Yes, that measure works fine. Thanks for that! 

But I'm not getting how powerBI is working. You say that every product_id is being counted once, so, if in a month the sales look like this:

sale     product_id 

sale1    10

sale2    10

sale3    11

with product table like this

product_id       price

10                     100

11                     110

then the sum of price column will give me 100+110 instead of 100+100+110?

Why is that? and make me even more confused the thing that if I use count of price instead of sum of price I get 80 in August 2019, and checking manually it happens that august 2019 has 758 sales... And I'm using "count" not "count(distinct)"...

[VahidDM]

Hi @ariel_skn 

use this measure instead:

Measure = 
Var _A = ADDCOLUMNS(sales,"P",RELATED(products[Price]))
return
SUMX(_A,[P])

When you use the Price and Date columns in your table, it will sum the Price of each Product_id onc.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/