Forum Discussion
How to create dimension tables correctly with this input?
Hi!
I have a table on a Excel file that looks like this:
I imported this table into the PowerBI Desktop and I dismantle it into two dimension tables (Year and KPI). See images below:
This is my fact table:
Now, for start, I would like to create some simple visuals to compare the companies on each KPI but my D_KPI table is not showing each individual KPI (screenshot below) instead it presents the whole Financial KPI column aggregated. Probably I can insert a filter but wouldn't be possible to isolate each KPI and select what I want to see in each visual with the dimension table?
Also, I would like to have something similar for the companies i.e have a dimensional table to select the company I want. I know that my input Excel does not have the more brilliant structure for this but is there a way to transform this data so I can create the visuals?
Below the relationships:
Here are some suggested modeling changes:
- Unpivot your company columns
- If you only have one column in your KPI table, you may not need it
- Not sure what the TTM rows are but you possibly could filter those out and calculate that value dynamically with a measure (or convert TTM to a date or integer value)
Pat
2 Replies
ppm1Solution Sage
Here are some suggested modeling changes:
- Unpivot your company columns
- If you only have one column in your KPI table, you may not need it
- Not sure what the TTM rows are but you possibly could filter those out and calculate that value dynamically with a measure (or convert TTM to a date or integer value)
Pat
Thank you ppm1!
With your hint to unpivot the company name tables I was able to make a way to create metrics with those values!
👍
