Help - Dataset Layout before importing to Power Bi

[Anonymous]

Hi community,

This is a tough one. Hope you could shed some light. Been a couple of days thinking about how to set this up and can't find the right approach. I am dealing with a large dataset that is arranged in a table format with the following headers (items):

1. Business Line (wealth management, capital markets, retail, etc.)
2. Product Line (loans, mortgages, advisory, etc.)
3. Category (depends on the product line -i.e.: Loans >$1M, Loans <$1M, etc.)
4. Region (by State and then the sum of all states shall be the Total)
5. Dates (monthly dates -i.e.: March 18, April, 18, May 18, and so on)
6. Balances (of each Date by each Business Line, Product Line, Category and Region)

The dataset is then composed of these six items and the are thousands of rows (monthly dates since 2004 up to 2020). This dataset is the one (or if break into smaller datasets are going to be the ones)  that I will import to Power Bi.

So, I want to arrange the dataset in more columns or multiple smaller datasets to make the following calculation with DAX (or any recommended feature):

1. Market share calculations: be able to recreate the market share (using the balance) that each Business Line has in a certain date and even filter it by Product, Category and Region.
2. Rank the top-performing Business Lines from 1st to 5th
3. Group certain Categories -i.e.: group loans all Loans < $5M.

Any hint or way to design the data-table in a way to be imported to Power Bi and recreate the foregoing?

[AlexisOlson · Accepted solution]

Having thousands of rows is not a problem for Power BI. The only reason I can think of to break it into multiple tables is to set up dimension tables for a nice star schema.

Relevant links on star schemas in Power BI:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it