New to PowerBI, is this possible?

[TARUGOKING]

Trying to build a report with PBD, any tips on how I can bring out the output below? TIA

[TARUGOKING]

Thank you all! 👍

[ajaybabuinturi · Accepted solution]

Hi @TARUGOKING,

You will follow the below steps to achive your requirement

1.Create a measures for Unique Student and Total Trip Count

Unique Student = DISTINCTCOUNT('Table'[Student])
Total Trip Count = SUM('Table'[Trip Count])

2.Drag the values to the Matrix chart

Columns: Trip Date (Only Yera and Months)

Values: Unique Student and Total Trip Count measures

3.Go to the Format panev> Values > Options and enable "Switch values to rows" radio button

4.By clicking down side fork arrow you will drill down the data till lowest level.

I am not sure on what basis you want to calaculate the avergae. You can create a Average Measure based on your business requirement.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[TARUGOKING]

Thank you. Snapshots helped me a lot. 

[DataNinja777]

Hi @TARUGOKING ,

Yes, this is definitely achievable in Power BI. To get the summarized view by month like in your image, first load the data table containing Student, Trip Date, and Trip Count into Power BI. Then, create a Calendar table using the following DAX code to generate the months:

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN('Trips'[Trip Date]), MAX('Trips'[Trip Date])),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date]),
    "Year", YEAR([Date]),
    "MonthYear", FORMAT([Date], "MMM YYYY")
)

Establish a relationship between the Calendar[Date] column and the Trips[Trip Date] column. Next, create three DAX measures to calculate the values:

Unique Student = DISTINCTCOUNT('Trips'[Student])

Total Trip Count = SUM('Trips'[Trip Count])

Average Trips = DIVIDE([Total Trip Count], [Unique Student])

Add a Matrix visual. Use Calendar[Month] as the column. To show multiple rows like "Unique Student", "Total Trip Count", and "Average", you can add all three measures into the Values section of the matrix. Power BI will stack them vertically, similar to your layout. If the month names are not sorting correctly, sort the Calendar[Month] column by Calendar[Month Number] to ensure chronological order. That's it. This setup gives you the exact output you're aiming for.

Best regards,

[FarhanJeelani]

Hi @TARUGOKING ,

You want a summary table by month that shows:

Unique Students

Total Trip Count

Average Trips per Student

Data columns:

Student

Trip Date

Trip Count (always 1)

Steps in Power BI Desktop:
Step 1: Load the Data
Load your Excel or CSV file with the three columns:

Student

Trip Date

Trip Count

Power BI will automatically convert Trip Date to date format.

Step 2: Create a “Month” Column
In Power Query or using a calculated column, extract the month name:

Month = FORMAT('Trips'[Trip Date], "MMM")

Or, for ordering, use:

Month = FORMAT('Trips'[Trip Date], "YYYY-MM")

Step 3: Create Measures
1. Unique Students

Unique Students = DISTINCTCOUNT('Trips'[Student])

2. Total Trip Count

Total Trips = SUM('Trips'[Trip Count])

3. Average Trips per Student

Average Trips =
DIVIDE(
[Total Trips],
[Unique Students],
0
)

Step 4: Build the Visual
Use a Matrix visual in Power BI:

Rows → Add a dummy field like "Metric" using "Enter Data" (or use a slicer).

Columns → Month

Values → Add your 3 measures:

Unique Students

Total Trips

Average Trips

To get the row labels like in Excel (Unique Student / Total Trip Count / Average), you can create a disconnected "Metric" table and use SWITCH logic, or you can stack them using UNION in a summary table.

Please mark this post as solution if it helps you. Appreciate Kudos.