DAX Power BI: Pivot table — number of customers by date

For example, let’s calculate the average number of customers per day in the table orders

The granularity of this table is an order (each row is a separate order). If we count the number of unique elements by E-mail, we get the total number of customers for all time. And we need to calculate the average number of customers per day, i.e. we need to convert the table to granularity expressed in days. The aggregation of the number of customers should be expressed in days: each row should not be a row by order, but a row by day, and for each day we should aggregate the number of customers. Then we can calculate the average for that column.

Let’s create a table in the data model based on the SUMMARIZE function.

We write SUMMARIZE: in the first parameter we specify our initial table Orders, and in the second parameter we specify the column by which we want to group data in the new initial table. We need to group data by days.

We have calculated the number of clients for each day, and now we can calculate the average for this column.

To do this, we will create a measure and write simple code: AVERAGE and in this function we insert our created column by day “Clients count”, and place our measure in the reports.

To work the measure without creating a temporary table, let’s combine together what we have written