cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

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

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.
Highlighted
Helper I
Helper I

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

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]

Highlighted
Community Support
Community Support

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

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.
Highlighted
Helper I
Helper I

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

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.
Highlighted
Community Support
Community Support

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

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors