Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hermes
Helper I
Helper I

sets of products in fact sale table / data model and DAX

Hi there,

 

I have a simple model with a table of sales facts and a table of product dimensions. Some of the products are actually sets of products.

 

In the fact table they appear as one item with its own quantity and value.

transaction idproduct idquantityvalue
1product1660
1product2690
2product112120
3set1170

 

In the dimension table, they appear as a separate item.

productprice
product110
product215
set170

 

In addition to these two tables, there is an additional table that defines the compositions of the set, i.e. the type and amount of products that make up the set.

setsparts of setquantity in set
set1product13
set1product23

 

My question is how to add to the model this third table with the definition of sets, so that the DAX function can report the amounts and sales values of products and products that are included in the set (so, only products specified in dim table, not sets)?

 

And how to prepare a DAX function that counts only the products and products included in the set ( quantity in set [sets] * quantity [transactions] ), not products and sets.

 

In addition, how to prepare a function that calculates the value of individual products after "breaking" the sets? (based on the proportions of prices of products that make up the set)

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

hi, @Hermes 

What is your expected output?

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

And I think you should create a relationship between dimension table and additional table by product id and sets.

Then create a measure by IF conditional.

 

Best Regards,

Lin

 

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

Hello,

 

My current output looks like this:

product nametransaction countsum of quantitysum of value
product1218180
product21690
set11170
sum325340

 

My desired output should look like this (no sets in product name field):

product nametransaction countsum of quantitysum of value
product1321 (18+3 from set)208 (180 + 28*)
product229 (6+3 from set)132 (90 + 42*)
sum330340

 

* calculation is based on share of value in product set, show in updated set table below:

setsparts of setquantity in setprice from productsvalue of prd in setvalue of setshare of value
set1product131030750,4
set1product231545750,6

value per product calculated like this:

set value from value[transaction] * share of value[sets table]

hi, @Hermes 

For your requirement(no sets in product name field), it's better to merge the fact table and additional table.

If you want to do it by dax, you also need to create a new "merge" table by dax.

For example, use this formula to add a new table

Table = UNION(FILTER(CROSSJOIN('fact',additional),'fact'[product id]=additional[sets]),FILTER(ADDCOLUMNS('fact',"sets",BLANK(),"parts of set",BLANK(),"quantity in set",BLANK(),"price from products",BLANK(),"value of prd in set",BLANK(),"value of set",BLANK(),"share of value",BLANK()),LOOKUPVALUE(additional[sets],additional[sets],'fact'[product id])=BLANK()))

Then use this formula to create two measrue

sum of quantity = CALCULATE(SUMX('Table',IF(ISBLANK([quantity in set]),[quantity],[quantity]*[quantity in set])))
sum of value = CALCULATE(SUMX('Table',IF(ISBLANK([quantity in set]),[value],[value]*[share of value])))

Result:

4.JPG

 

here is pbix file, please try it.

 

Best Regards,

Lin

 

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

Thank you for the proposed solution.
 
This is an option that I have considered, but I try to avoid it, mainly because such a combination of tables significantly multiplied the number of rows to be stored.
 
In addition, the sales table is connected to a table calculating the price structure from our ERP system and I am afraid that when duplicating oryginal rows id's I will get incorrect data from these calculations.
 
That's why I'm looking for a DAX-based solution without table joins.

hi, @Hermes 

For your case, I think this is a great way by dax, since you don't want to show set1 in visual.

Or if you could use merge the tables in Edit Queries?

https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d1...

 

Best Regards,

Lin

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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