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
File Link: https://www.exceljetconsult.com.ng/wp-content/uploads/2021/09/pbi.xlsx pbi
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
