Skip to main content
cancel
Showing results for 
Search instead 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

Reply
lauralovesBI
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
v-jianboli-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors