Calculate Sales Cost Value Based on 2 columns from different reference tables

[lauralovesBI]

hi! I am sure this will be an easy fix but I can't quite get the DAX correct.

I have 3 reference tables for customers and products and 2 fact tables with company product cost (over time) and consumer product sales (over time). I want to calculate our company's cost of the products (not the value of the consumer sales). My current DAX is:

Sales Value of Product by Consumer = SUMX(VALUES(Products[UniqueProductRef],[Product Cost]*[Consumer Sales Qty]

This identifies the product but I also need to identify the unique consumer name as well. How do I add that unique consumer column so the calculation identifies the unique product cost and the unique consumer sales?

[v-jianboli-msft]

Hi @lauralovesBI ,

What is the relationship between the product, the company, and the customer? What is [Product Cost] and [Consumer Sales Qty], Measure or column? If they are measures, What exactly do they look like?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[lauralovesBI]

hi!

Product = reference table of what we sell, the company = who i work for, customer = who we sell to,
Product cost = our cost of the product to produce it (changes over time, not static; sum of cost column), Consumer sales qty = how much the end customer purchases (SUM of sales qty column)

these are in a star schema where the product, consumer names, date table are all references linked to the dimension tables which are consumer sales, product costs

problem is: i need to identify the product cost as it changes over time and relate it the individual consumer; so there are two columns that need referenced in the calulation: VALUES(Products[UniqueProductRef]) and VALUES(Consumer[ConsumerName]). How do I use VALUES twice in a calculation?

Thank you!!