cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

dimensional data modelling design - Data warehouse

I am having

dimension tables

item (item_id,name,category)
Store(store_id,location,region,city)
Date(date_id,day,month,quarter)
customer(customer_id,name,address,member_card)
fact tables

Sales(item_id,store_id,date_id,customer_id,unit_sold,cost)
My question is if I want to find average sales of a location for a month Should I add average_sales column in fact table and if i want to find sales done using the membership card should I add corresponding field in fact table?

dimensional data modelling design - Data warehouse
My understanding so far is only countable measures should be in fact table so I guess membership_card should not come in fact table.

Please let me know if I am wrong.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: dimensional data modelling design - Data warehouse

Hey,

 

you don't have to add the average to you fact table. Using Power BI there are 2 two ways to show the average

  • just use the column unit_sold_cost in any visual and change the aggregation function that you want, it's also possible to use the same column more than in once in the same visual with different aggregation functions
  • just create a simple DAX statement to create a measure on top of your fact table like so avg cost = CALCULATE(AVGERAGE('nameofyourfacttable'[unit_sold_cost]))

Regarding your question about transactions using the membership card, this is a difficult question, because even if you that a customer owns a membership card this does not necessarily mean he uses this membership card. For this reason you may implement another information in your facttable, "used membership card".

 

Hope this answers some parts of your question

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
Highlighted
Super User III
Super User III

Re: dimensional data modelling design - Data warehouse

Hey,

 

you don't have to add the average to you fact table. Using Power BI there are 2 two ways to show the average

  • just use the column unit_sold_cost in any visual and change the aggregation function that you want, it's also possible to use the same column more than in once in the same visual with different aggregation functions
  • just create a simple DAX statement to create a measure on top of your fact table like so avg cost = CALCULATE(AVGERAGE('nameofyourfacttable'[unit_sold_cost]))

Regarding your question about transactions using the membership card, this is a difficult question, because even if you that a customer owns a membership card this does not necessarily mean he uses this membership card. For this reason you may implement another information in your facttable, "used membership card".

 

Hope this answers some parts of your question

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors