cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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 Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors