Data Modelling Help

[id013]

Hi,

I have a bit of conundrum with a data model I'm working on. So it's a dashboard that shows sales values for different customer demographics. 

So I have a customer base table, that shows various combinations of customer attributes and their customer base size. 

For example:

Age	Sex	City	Municpality	Customer Base Size
12-Oct	M	Vancouver	Kitsilano	15

I have another table that shows these same attributes and the sales values of different products per day. 

For example:

Date	Age	Sex	City	Municpality	Sales Product 1	Sales Product 2	Sales Product 3	Sales Product 4
May 1 2024	10 to 12	M	Vancouver	Kitsilano	1	6	0	1
May 2 2024	10 to 12	M	Vancouver	Kitsilano	4	5	8	1

I want to create a matrix that shows these customer attributes, and their respective sales values but also show how it compares to the customer base, for example for all the 10-12 year olds. 

Date	Age	Sex	City	Municpality	Population	Sales Product 1	Sales Product 2	Sales Product 3	Sales Product 4
May 1 2024	10 to 12	M	Vancouver	Kitsilano	15	1	6	0	1
May 2 2024	10 to 12	M	Vancouver	Kitsilano	15	4	5	8	1

The population is created using a sum of the population from the population table, while the sales values are taken from the sales table.

I can join these tables together using the attributes to join them. However I'm running into a problem with teh date. So basically depending on teh date range, certain attribute combinations didn't buy anything so they wouldn't be represented in the dataset. Which means the population wouldn't get added into the matrix for that date. So the simplest solution would be to just have those rows show up with 0 sales values so they would still be able to join with the population table, but I'm wondering if there's a better way to organize the data. This is how it comes to me but I may be able to request changes. 

I should also mention that they want personalized visuals, so I can't just create specific population measures as they have to be dynamic based on what fields are chosen in the personalized matrix.

Thanks

[lbendlin]

Looks like Power Query was too eager to convert the text "10-12'  into a date.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...