cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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?

2 REPLIES 2
Community Support

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?

Refer to:

How to provide sample data in the Power BI Forum

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.

Frequent Visitor

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!!