Getting a Distinct Avg from a separate table

[Anonymous]

I have two tables, one with number of units sold, and one with item cost.  They are joined by the Item ID.   

The Inventory table  has multiple rows with the items sold, so you would see a "Popcorn Sold"  on several different dates.  

I cannot figure out how to get a Total Cost.   When I try to do Units x Cost,  it is defaulting to Count.   When I do any other function, such as Average,  it is taking the Average of ALL Costs, not just the matching item.   Since it is in two tables, I don't think I can use AverageX.   

Are there any ideas?   Appreciate the help.  

[Anonymous]

Hi  @Anonymous ,

You can try to change the relationship between tables to Both filtering.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#cross-filter-direction

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks. I am new to Power BI and the community. Will share what I can.  

Here is what the data looks like in the transaction table:  

Here is what it looks like in the ItemsCosts Table.  (Covered costs for proprietary reasons.) 

Hre is what it looks like on the dahsboard: 

I cannot get Cost of the particular item to match up with the item transaction.   If I just pull in the "Cost" field,  it reverts to count.   If I try something like here where it is "Minimum"  it takes  the minimum cost of all items, which is zero for a Cup of Water.  

Can't figure out how to get the cost for JUST the one item, Bottomless Popcorn.  

Is this enought information?  

[Anonymous]

Hi  @Anonymous ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.