# Average Daily Total in Excel & PowerBI

Welcome to Excel Jet Consult blog post. In this Excel and PowerBI video tutorial. we learn how to calculate Average Daily Total using DAX. If you enjoy this video, consider subscribing to this channel

Video

#### Sample Data

We have the sample data as seen below. The data is formatted as an Excel Table and named Sales

#### Create PivotTable and Add to Data Model

Next, we want to use one stone to kill two birds. To create PivotTable and add data to the data model,

• Select a single cell in the dataset
• In the Tables group of the Insert tab, click on PivotTables
• In the PT dialogue box, select Existing Worksheet and choose cell F2 as the Location
• Check Add this data to the Data Model as seen below

• Click OK

#### Create DAX Measures

Next, we want to create three DAX Measures. To do that,

• Right-click on the Sales and choose Add Measure

• In the Measure dialogue box, give Total Sales as Measure Name
• In the Formula, enter the following =SUM([Total])

• Click OK
• Again, right-click on the Sales and choose Add Measure
• In the Measure dialogue box, give Count of Distinct Name as Measure Name
• In the Formula, enter the following =DISTINCTCOUNT(Sales[Date])

• Click OK
• Again, right-click on the Sales and choose Add Measure
• In the Measure dialogue box, give Average Daily Total as Measure Name
• In the Formula, enter the following =DIVIDE([Total Sales],[Count of Distinct Date],BLANK())

• Click OK

#### Build PivotTable Report

Finally, we want to build our PivotTable to display Total Sales, Count of Distinct Dates and Average Daily Total measures by Year

• Drag and Drop Total Sales, Count of Distinct Dates and Average Daily Total measures inside the Values area
• Drag and drop the Date field into the rows area and remove Quarters and Months

From the screenshot below, we can see that we recorded \$320,228 as the Average Daily Total for all the transaction for year 2020

Posted in Blog.