Need help organizing data model (images of sample data included)

[KennethN]

Hey folks, I could use some assistance on how I should set up these tables for my model in Power Bi to make it easiest to pull the various metrics I need. Below is a screenshot of some fictitious data that loosely mimics the raw data that I’m working with.

The data is derived from various reports we file, and includes a variable amount of companies tied to those reports. You’ll notice that for reports where there are two or more companies, the data in the first 4 columns is replicated for each row, as each company needs to be tied to a specific REPORT ID. Next to each company name, you’ll see an Office column. Each of those Offices are assigned to a region.

On a monthly and quarterly basis, there are several metrics that we need to derive from this data including, typically involving the total AMOUNT and number of reports submitted for the month/quarter broken down by

• TYPE
• CATEGORY (The various report TYPE’s are grouped into 1 of 3 CATEGORIES)
• OFFICE
• REGION (Each OFFICE is tied to a certain REGION)

The dataset is not significantly large (about 4,000 rows or so), but I’m trying to future proof our metrics process for when the volume of reports increases and if new information is added to the raw data (Additional columns, added report TYPE, new OFFICE’s or REGION’s, etc.). I’m new to the modeling process but I was considering using Power Query to break this down into a star schema so that it can be modular and make updates to the report much easier.

The question I have is, what is the best way to organize this? I’m considering using 2 FACT tables with DIM tables to filter and slice for the various bits of metrics we need. Here’s a visual of what I’m thinking.

Much of the thought process for two FACT tables comes down to my inexperience with complex measures, and needing to create Matrix tables that do SUMs and percentage of Grand Totals but ALSO (for Regions only) include items with no data (I’ve found that when I use measures, I can’t select the option to include items with no data. But I don’t know what the best way to organize this is. To give an example of metrics I’d like to generate, let’s assume I’m doing Q1 2025.

Any thoughts, tips, guidance you can provide would be helpful. If you have additional questions about the data, I’m happy to answer them.

[DataInsights · Accepted solution]

@KennethN,

Here's how I would approach this, building on the dimension tables you've created.

1. In Power Query, model the two tables as shown below:

FACT_ReportData

(no duplicate rows)

ReportID_Entity_Office

(this is a bridge table to join DIM_Region and FACT_ReportData)

2. Create relationships:

(all relationships are unidirectional)

3. Create measures:

Amount = SUM ( FACT_ReportData[AMOUNT] )

Amount by Office = 
CALCULATE (
    SUMX ( VALUES ( DIM_Region[OFFICE] ), [Amount] ),
    CROSSFILTER ( FACT_ReportData[REPORT ID], ReportID_Entity_Office[REPORT ID], BOTH )
)

Amount by Region = 
CALCULATE (
    SUMX ( VALUES ( DIM_Region[REGION] ), [Amount] ),
    CROSSFILTER ( FACT_ReportData[REPORT ID], ReportID_Entity_Office[REPORT ID], BOTH )
)

The CROSSFILTER function enables bidirectional filtering in the context of the measure. Additional measures can be created using this pattern.

4. Visuals:

[KennethN]

I can see how this would work, but the last 2 pieces of the puzzle is how would I alter this solution to allow getting the percentage of the total (columns circled in the image below) since I need to see results with no data?  I imagine I would need to create a seperate measure for each of those columns?

Thank you so much for the assistance here, this has been very helpful, I'm at the home stretch!

[DataInsights]

@KennethN,

Glad to hear that! You'll need additional measures for those calculations:

% Amount by Region = 
VAR vNumerator = [Amount by Region]
VAR vDenominator =
    CALCULATE ( [Amount by Region], ALLSELECTED ( DIM_Region[REGION] ) )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

Number by Region = 
CALCULATE (
    SUMX ( VALUES ( DIM_Region[REGION] ), CALCULATE ( DISTINCTCOUNT ( ReportID_Entity_Office[REPORT ID] ) ) ),
    CROSSFILTER ( FACT_ReportData[REPORT ID], ReportID_Entity_Office[REPORT ID], BOTH )
)

% Number by Region = 
VAR vNumerator = [Number by Region]
VAR vDenominator =
    CALCULATE ( [Number by Region], ALLSELECTED ( DIM_Region[REGION] ) )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

Set the REGION field to show items with no data:

[Anonymous]

Hi @KennethN 

First of all, I need to confirm with you whether you have the data of these five tables now, or are the other three tables what you expect to get except the two “Fact” tables?

Then your expected result is this?

But I have some questions about the expected results. You said "assuming I'm doing data for Q1 2025", but the 2299 of Initial in the category metric appears to be data for Q3 2024.

In your Office Metrics, how did you get the data of 4000 for Japan? In my opinion, it should be 3886+1701 = 5587.

Please feel free to correct me if I understand something wrong.

Best Regards,
Yulia Xu

[KennethN]

I'm embarrassed, apologies Yulia. I changed some of the data half way through making the post to replicate some of the issues that I have, but didn't change the data everywhere so that must have been VERY confusing. I really do apologize!

To answer your questions:

First of all, I need to confirm with you whether you have the data of these five tables now, or are the other three tables what you expect to get except the two “Fact” tables?

Yes, I have the data of these 5 tables now. These are all generated in Power Query first from the Raw data.

---

Then your expected result is this?

But I have some questions about the expected results. You said "assuming I'm doing data for Q1 2025", but the 2299 of Initial in the category metric appears to be data for Q3 2024.

In your Office Metrics, how did you get the data of 4000 for Japan? In my opinion, it should be 3886+1701 = 5587.

---

You are 100% correct. Again my apologies! Should have finished my coffee before posting. Just updated the Expected Results below.