cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors