Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 id | product id | quantity | value |
1 | product1 | 6 | 60 |
1 | product2 | 6 | 90 |
2 | product1 | 12 | 120 |
3 | set1 | 1 | 70 |
In the dimension table, they appear as a separate item.
product | price |
product1 | 10 |
product2 | 15 |
set1 | 70 |
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.
sets | parts of set | quantity in set |
set1 | product1 | 3 |
set1 | product2 | 3 |
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)
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
Hello,
My current output looks like this:
product name | transaction count | sum of quantity | sum of value |
product1 | 2 | 18 | 180 |
product2 | 1 | 6 | 90 |
set1 | 1 | 1 | 70 |
sum | 3 | 25 | 340 |
My desired output should look like this (no sets in product name field):
product name | transaction count | sum of quantity | sum of value |
product1 | 3 | 21 (18+3 from set) | 208 (180 + 28*) |
product2 | 2 | 9 (6+3 from set) | 132 (90 + 42*) |
sum | 3 | 30 | 340 |
* calculation is based on share of value in product set, show in updated set table below:
sets | parts of set | quantity in set | price from products | value of prd in set | value of set | share of value |
set1 | product1 | 3 | 10 | 30 | 75 | 0,4 |
set1 | product2 | 3 | 15 | 45 | 75 | 0,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:
here is pbix file, please try it.
Best Regards,
Lin
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?
Best Regards,
Lin
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |