0

I have 8 tables with data of sold products. Each table is about a unique product. In Power BI, I want to create a matrix, containing the sold quantities (values) per product (rows), per month (columns), and the number of unique customers who bought the products.

Each of the 8 tables with the sales data has the following structure. So the App ID is different for each table, but is constantly the same within a table. Example for a Cars table:

Customer ID   Month    App ID
29273         2020-3   1
90283         2018-5   1
55824         2016-12  1
55824         2018-10  1
55824         2021-1   1

So, a bicycle table would have the same structure, but then the App ID's would be, for example 2, in the entire table.

I have two tables that are connected with the 8 product tables in a one-to-many relationship. The Calendar table based on the Month column, and the App table based on the App ID column.

The table Calendar:

Month
2015-1
2015-2
2015-3
2015-4
2015-5
...
...

The table Apps:

ID   Name
1    Cars
2    Bicycle
3    Scooter
4    ...

So, the structure is:

enter image description here

I created the Calendar en Apps tables so that I could use them for the matrix, but it doesn't work like I want so far. At the end, I want to create a matrix like this (where P = the number of products sold, and C = the number of customers in that month for that product):

Product       2015-1  2015-2  2015-3  2015-4  2015-5 ...
              P  C    P  C    P  C    P  C    P  C
Cars          3  2    5  5    7  6    2  1    4  2
Bicycle       11 9    17 14   5  5    4  4    8  6
Scooter       ...
Skateboard    ...

As mentioned, I made that Calendar and App table so that I can use the columns from it to fill the labels in the rows and columns. What I am unable to do is create such a 'general variable' of the number of products sold per product, and the number of customers associated with it.

Can someone explain to me how I can fill the matrix with the numbers of products (and customers) sold, so that the matrix looks like the one described above?

lakeviking
  • 322
  • 1
  • 6
  • 18

1 Answers1

0

I think this is pretty straight forward. You actually don't need the 'Calendar' table as it only contains the same info as is already in the 'Sales' table.

You should configure the matrix like this:

  • Rows: 'Name' (from the 'Apps' table)
  • Columns: 'Month' (from the 'Sales' table)
  • Values:
    1. C = Count distinct of CustomerId (from 'Sales' table) [this counts the unique customers per month and app)
    2. P = Count of CustomerId (from 'Sales' table) [this counts the rows of the 'Sales' table which is your number of products if every row represents 1 sale)

enter image description here

The different aggregations (count distinct, count) can be found under the Values' options: enter image description here

Andreas Wendl
  • 594
  • 5
  • 7
  • Hello Andreas, thanks for your answer! The problem is, I don't have 1 big Sales table, but I have 8 different sales tables. One for each product. So I also have 8 Month columns that must be used in the matrix. That's why I created the Calendar column. Then I can use the Month column of the Calendar table for the columns in the matrix. Same problem for the values. So I think I need a general value for the (distinct) counts, like I have for the Month and for the App ID with the dimension tables. But I have no idea how to do that – lakeviking Jan 22 '22 at 09:22
  • You could use the 'append' function and append all 8 sales tables into one big sales table (if the schema of all 8 tables is the same). If you need the name of the product (I guess that's the table names) you can just add a static column with the product name before appending. If your data source supports SQL like queries you could also do that in the query to load the data from the source or even directly create a view on the source. – Andreas Wendl Jan 22 '22 at 14:46