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.
Hello,
i am thinking about making the switch from Qlikview to Power Bi and at the moment reading up on how to make a good model.
Now i have this to think through.
I have a FactInvoice table:
Customer ID | SKU ID | qty | price |
1 | 1 | 3 | 4,5 |
1 | 2 | 2 | 5 |
2 | 1 | 3 | 4,5 |
2 | 2 | 2 | 5 |
3 | 3 | 4 | 3,5 |
... |
|
DimCustomer: (this in total has
Customer ID | Customertype |
1 | Retail |
2 | Project |
3 | e-commerce |
4 | Wholesale |
5 | other |
DimProduct:
SKU ID | ProductCategory |
1 | Tableware |
2 | Furniture |
3 | Decoration |
4 | Candles |
5 | Textile |
6 | Wall art |
7 | Other |
Now i need to show total turnover out of all the invoices in 4 figures, each representing customers or products needing a different approach:
1 = Turnover for all Customers where Customertype = "Project" and ProductCategory = "Tableware"
2 = Turnover for all Customers where Customertype = "Project" and ProductCategory = "everything except Tableware"
3 = Turnover for all Customers where Customertype = "everything except Project" and ProductCategory = "Tableware"
4 = Turnover for all Customers where Customertype = "everything except Project" and ProductCategory = "everything except Tableware"
So managent want's to have a look at 4 figures based on a logic that is not 100% based on slicing by 1 dimension.
Reason is they are not ready to rethink used dimensions at this moment but want to have an alternative clustering.
I understand that these 4 figures can be calculated with measures based on turnover in the FactInvoice table and filtered based on the dimensions in the 2 dimension tables.
And if i then make a table with dimension year and these 4 measures evolution can be viewed for these amounts.
But what if other metrics want to be viewed for these new 4 categories of customer/product combinations?
for example:
-how many customers in each of the 4 groups
-amount of outstanding orders for these 4 groups
-...
That would mean there is a need to have these 4 categories as a seperate dimension field?
But since it is a dimension based on a combination of facts customertype and productype how would i need to tackle this?
Should i add logic to the FactInvoice table so that on every invoice line i have an indication to which of the 4 categories the line belongs to?
But if i do it like that am i then not creating a dimension in a fact table?
Could someone help me solve this?
thanx a lot!!
Chris
Solved! Go to Solution.
@Chris_Cools , Create a new column in your fact
Switch(True(),
Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) = "Tableware" , 1 ,
Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) <> "Tableware" , 2 ,
Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) = "Tableware" , 3 ,
Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) <> "Tableware" , 4
)
Add other rules if needed
You can now use this column from you fact.
The same can be done in power query, by merging the tables and getting these columns in the fact
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
or using
Power Query Table.AddJoinColumn: https://youtu.be/-mThmfSaiV8
@Chris_Cools , Create a new column in your fact
Switch(True(),
Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) = "Tableware" , 1 ,
Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) <> "Tableware" , 2 ,
Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) = "Tableware" , 3 ,
Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) <> "Tableware" , 4
)
Add other rules if needed
You can now use this column from you fact.
The same can be done in power query, by merging the tables and getting these columns in the fact
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
or using
Power Query Table.AddJoinColumn: https://youtu.be/-mThmfSaiV8
Hello Amitchandak,
Thank you very much for your reply and i will test this solution!
But i was wondering, when done like this does this go against the logic of not having dimensions in the fact table?
Or is this no issue at all?
kind regards,
chris
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |