Pivoting Data with a measure

[axel_BI]

Hi,

my data is presenting me the sales per article per day per location. Like this.

Location|Date|Article_Nr|Sales

1000	05.08.2019	100	41,92 €
1000	05.08.2019	200	2,76 €
1000	05.08.2019	300	5,51 €
2000	05.08.2019	100	10,00 €
2000	05.08.2019	300	9,25 €
3000	06.08.2019	100	28,22 €
3000	06.08.2019	200	111,59 €
1000	06.08.2019	100	56,45 €

There are multiple locations and multiple articles per day. But not each locations will appear each day. And not every article will be sold in every day.

I need a way to Sum up data per Day per location.

I also need to count the days with sales for each location.

My goal is, to achieve average Sales per Day in total, aswell as per location.

I only get a general Average of Sales per Day, which doesnt consider different day-count per location.

Basically distinctcount location for distinct date.

Thx

[Piotraq · Accepted solution]

You can use summarize:

Summarize(Table;

Table[Location];
Table[Date];

"Sum";Sum(Sales);
"AVG";average(Sales))

[axel_BI]

Thank You,

the pivot-table works just fine