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.
Hi All,
Never posted a question on Power BI Community but I'm feeling really stuck.
I have two fact tables and 2 dimension tables.
My fact Tables: Marketing Costs & All Orders.
When I create visualiztions based on Channel (the field the tables are related by) the data breaks it down properly. BUT when I create a visualization based on Month I get the total of all months of marketing costs for every month. The only way it shows me a proper breakdown is by channel.
Can someone please direct me on what I'm doing wrong!!
I have tried many changes, having already reconstructed my data model into something more similar to a star schema...
Solved! Go to Solution.
@Pragati11 I found a solution. I restructured my data and now everything is pulling correctly. See attached image.
I really appreciate your help with this!
Hi @Pragati11 , the month column is from a date field in the orders table but there is a date field in the marketing costs table as well.
Hi @CF25 ,
I suggest you introduce a CALENDAR table in your data model. Connect this table to both of your tables - Orders and Marketing Costs.
Then use Month column from Calendar table and metrics from other 2 tables on your report for visualisation.
The reason for using a CALENDAR table is it will avoid any data in-consistencies in other 2 tables like there could be data for a certain day/month in ORDERS table but not in Marketing table.
Try this approach and see if this works.
As part of best practices one should have a CALENDAR date table in Power BI for such reporting purposes.
You can refer following article on creating Calendar table:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Thanks,
Pragati
@Pragati11 , thanks for your reply. I actually had tried that already. Power BI wont allow me to connect the tables by the column Channel AND through another table by date. I tried just having the calendar table connect to order table and to marketing costs table. Then it would show a proper breakdown of the months but an incorrect breakdown of the channel.
Any insight on this?
Hi @CF25 ,
Are you looking for 2 different breakdowns?
If yes, then may be create summarised measures at Month and then at Channel level; then further use them in your report.
If it is possible share some sample data or your pbix file by removing any sensitive information from them.
Thanks,
Pragati
@Pragati11 I found a solution. I restructured my data and now everything is pulling correctly. See attached image.
I really appreciate your help with this!
When I created a calculated column of summarising costs by Month - it would not show up at all on the chart.
Really appreciate your help with this @Pragati11 !
Yes, for now I want the 2 different breakdowns...but I would imagine there will be other ways I'd like to play around with it.
I'm going to try those measures and see how it works.
For now see attached
file:///C:/Users/feigelstein/Downloads/marketingcosts%20-%20sample.htm
file:///C:/Users/feigelstein/Downloads/orderdata%20-%20sample.htm
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |