Аutomatically breakdown of the total amount by months by adding new columns to a table

[Anonymous]

Hello Community,

I have the following table:

PRODUCT	ORDERED QUANTITY	SOLD QUANTITY	DELIVERED QUANTITY
PRODUCT A	100 UNITS	90 UNITS	50 UNITS
PRODUCT B	200 UNITS	150 UNITS	100 UNITS

I have also a date slicer with the following dates:

01.01.2018 – 01.10.2018.

The abovementioned quantites are summed for the whole period.

What I want is the column SOLD QUANTITY to be dynamically broken down monthly. Since the slicer selection amounts to 10 months, 10 new columns should be automatically added.

PRODUCT	ORDERED QUANTITY	SOLD QUANTITY	DELIVERED QUANTITY	01.01.2018	01.02.2018	01.03.2018	01.04.2018	01.05.2018	01.06.2018	01.07.2018	01.08.2018	01.09.2018	01.10.2018
PRODUCT A	100 UNITS	90 UNITS	50 UNITS	5	10	10	15	5	10	15	5	5	10
PRODUCT B	200 UNITS	150 UNITS	100 UNITS	20	10	20	10	15	10	15	20	20	10

Therefore, If I choose, for example, 01.01.2017 – 01.05.2018, then 17 new columns should be automatically added and so on.

Is this somehow possible in POWER BI?

Thank you in advance.

Best regards,

[Ashish_Mathur]

Hi,

How does one know how much quantity should be sold in every month?

[Anonymous]

Hi Ashish,

It's my fault, cause I forgot to submit the raw data table.

The first table:

PRODUCT	ORDERED QUANTITY	SOLD QUANTITY	DELIVERED QUANTITY
PRODUCT A	100 UNITS	90 UNITS	50 UNITS
PRODUCT B	200 UNITS	150 UNITS	100 UNITS

is actually a summarized table whose underlying data consist of daily sales information. For example:

DATE	PRODUCT	SOLD QUANTITY
04.01.2018	PRODUCT A	1
05.01.2018	PRODUCT A	1
15.01.2018	PRODUCT A	1
20.01.2018	PRODUCT A	1
30.01.2018	PRODUCT A	1
07.02.2018	PRODUCT A	3
15.02.2018	PRODUCT A	4
17.02.2018	PRODUCT A	1
22.02.2018	PRODUCT A	1
27.02.2018	PRODUCT A	1
....	.....	.......

I have seen that I messed up the second table. The new columns that I want to be added automatically, should be summarized by months, therefore the tablе would look like this:

PRODUCT	ORDERED QUANTITY	SOLD QUANTITY	DELIVERED QUANTITY	01.2018	02.2018	03.2018	04.2018	05.2018	06.2018	07.2018	08.2018	09.2018	10.2018
PRODUCT A	100 UNITS	90 UNITS	50 UNITS	5	10	10	15	5	10	15	5	5	10
PRODUCT B	200 UNITS	150 UNITS	100 UNITS	20	10	20	10	15	10	15	20	20	10

Best regards,

[v-jiascu-msft]

Hi @Anonymous,

Please download the demo in the attachment. You can use a Matrix visual. But I'm afraid it can't be the same with yours.

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft,

Thank you for your reply. Unfortunately, this is not what I am looking for.

I want to have in one table summarized data by product, but a particular column to be additionally and automatically broken down monthly.

Kind regards,

[v-jiascu-msft]

Hi @Anonymous,

I'm afraid the Table visual can't add columns itself automatically. The Matrix has the monthly columns and it can be filtered by a slicer. Is this enough? Please refer to the snapshot below.

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft,

Unfortunately, Matrix will break down each column not only the SOLD one. One table + one matrix, however, is not accetable for the client.

Thank you for your response.

Best regards,

Georgi