Dear Colleague,
Good morning!
I just started learning Power BI and facing one problem need to find the solution.
My Question How to Write dax code for Average of Tour:1 , Tour 2, etc.
So, Idea is that i want to create table where I add more two rows with total and Average cust.
Something like this:
Regards,
Hemant
Hi @hement2
It would be great if you could provide a sample PBIX file.
Looking at the structure of your data, you will need to unpivot all the columns that have the Tour times. You can then create one measure to get the Average calculation you want and another which will Sum the column
Hello @hement2 , and welcome to EnterpriseDNA and starting a conversation in the Community Forum.
While we await a sample work-in-progress Power BI Desktop file, I thought that I would contribute some small help to the conversation. Without your sample file, I used a smaller data set to create the Report Visual similar to that which you asked in your question.
Things to remember:
The Report Visual is a Table, and you may add formatting to the table, and DAX measures if you choose.
Do note that the Average Row displayed is rounded to a whole number, though that can be adjusted by formatting the DAX if required.
Please use the attached file as the logic in your development efforts.
We look forward to your final solution. Good Luck!
customer tour report.pbix (53.2 KB)
If you would like to add an average row to a table using DAX in Power BI, you can follow these steps:
1.1. Create a measure for the average values you want to display. For example, if you want to calculate the average of ‘Column2’ and ‘Column3’, you can use the following DAX expression:
AverageColumn2 = AVERAGE(YourTable[Column2])
AverageColumn3 = AVERAGE(YourTable[Column3])
Replace ‘YourTable’ with the name of your table.
1.2. Create a new table that includes the original table data along with the average row.
Use the UNION function in DAX to combine the two tables. Here’s an example:
UnionTable = UNION(VALUES(YourTable), ROW("Column1", "Average", "Column2", [AverageColumn2], "Column3", [AverageColumn3]))
This expression uses the VALUES function to retrieve all the distinct values from ‘YourTable’. Then, the ROW function is used to create a new row with the ‘Average’ label and the average values from the measures.
1.3. Display the UnionTable in a visual or use it for further analysis.
Please note that DAX is primarily used in Power BI and Excel Power Pivot for creating measures and calculations. DAX does not directly modify the structure of the underlying tables like SQL does with the UNION ALL function.
If you follow the logic in the Power BI Desktop file, you will see that I used the same pattern and extended the UNION to include an average and a total row.
The DAX measure for the resulting UNION Table is as follows:
Counts Table With Sum and Average =
UNION (
VALUES ( tblCounts ),
ROW (
"Column1", "Total",
"Column2", [SumColumn 07:00],
"Column3", [SumColumn 08:00],
"Column4", [SumColumn 11:00],
"Column5", [SumColumn 15:00],
"Column6", [SumColumn 20:00]
),
ROW (
"Column1", "Av. Customer",
"Column2", [AverageColumn 07:00],
"Column3", [AverageColumn 08:00],
"Column4", [AverageColumn 11:00],
"Column5", [AverageColumn 15:00],
"Column6", [AverageColumn 20:00]
)
)
SampleFile.pbix (81.1 KB)
Dear @ystroman and @DavieJoe ,
Thanks for the quick response. We are almost very close to a result.
I would like to share a sample file of pbix as created. I cannot share the original file due to confidentiality. It is a real problem for which I’m looking for a solution.
Kindly advise how to achieve the required solution.
As I shared the early screenshot, which I was used to creating in Excel separately and was attaching with the report, I think I can do this in Power Bi. So, I come to an approximate solution but am stuck at" **the average customer on Each tour.**"
Reagrds,
Hemant
Hello @hement2,
The updated powerquery-m transforms and pivots the Tour Columns and uses Numpax Columns as values. Pivoting in this way takes care of the Values aggregation. Please follow the logic and measure patterns to create the Average and Totals, which will be used in the UNIONTABLE as shown in the previous post.
SampleFile (ysman Table Visual Pivoted, now needing DAX Total and AVG fow added to visual).pbix (89.0 KB)
// This code imports data from an Excel file and transforms it into a table with dates and tour numbers as columns and the number of passengers as values
let
// Source is the Excel file path
Source = Excel.Workbook(File.Contents("C:\Users\user\Desktop\SampleData.xlsx"), null, true),
// Sheet1_Sheet is the data from the first sheet of the Excel file
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
// Replaced Value replaces any "Tour " with "Tour " in the fifth column
#"Remove any extra spaces between Tour and Number" = Table.ReplaceValue(Sheet1_Sheet,"Tour ","Tour ",Replacer.ReplaceText,{"Column5"}),
// Promoted Headers makes the first row of the table as headers
#"Promoted Headers" = Table.PromoteHeaders(#"Remove any extra spaces between Tour and Number", [PromoteAllScalars=true]),
// Changed Type converts the data types of each column to date, text or integer
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Service Type", type text}, {"Numpax", Int64.Type}, {"Booking Ref.", type text}, {"TourNbr", type text}}),
// Removed Columns removes the columns that are not needed for the analysis
#"Removed Remove Service Type and Booking Ref." = Table.RemoveColumns(#"Changed Type",{"Service Type", "Booking Ref."}),
// Reordered Columns rearranges the columns in a logical order
#"Reordered Columns" = Table.ReorderColumns(#"Removed Remove Service Type and Booking Ref.",{"Date", "TourNbr", "Numpax"}),
// Pivoted Column pivots the table by TourNbr and aggregates the Numpax by sum
#"Pivoted Tour Columns with Numpax as Values" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[TourNbr]), "TourNbr", "Numpax", List.Sum),
// Replace any null values with 0 in each tour 11:00 column
#"Replace nulls in 11:00 Column" = Table.ReplaceValue(#"Pivoted Tour Columns with Numpax as Values",null,0,Replacer.ReplaceValue,{"Tour 11:00"}),
// Replace any null values with 0 in each tour 07:00 column
#"Replace nulls in 07:00 Column" = Table.ReplaceValue(#"Replace nulls in 11:00 Column",null,0,Replacer.ReplaceValue,{"Tour 07:00"}),
// Replace any null values with 0 in each tour 20:00 column
#"Replace nulls in 20:00 Column" = Table.ReplaceValue(#"Replace nulls in 07:00 Column",null,0,Replacer.ReplaceValue,{"Tour 20:00"}),
// Replace any null values with 0 in each tour 08:00 column
#"Replace nulls in 08:00 Column" = Table.ReplaceValue(#"Replace nulls in 20:00 Column",null,0,Replacer.ReplaceValue,{"Tour 08:00"})
in
#"Replace nulls in 08:00 Column"
Thank you so much.
