Group values per user and show them on a weekly average basis

[Anonymous]

Hi, 

I have this excel sheet see below:

I would like to show the weekly average clicks per UserID (all clicks figured up, by User ID) on a weekly basis. 

Can anyone support me on this topic?

Thanks in advance! Much appreciated!

Regards, 

Paul

[123abc · Accepted solution]

To calculate the weekly average clicks per UserID in Power BI using DAX, you can follow these steps:

1. Load your Excel data into Power BI.
2. Create a new table or use an existing one to display your results.
3. Use DAX functions to calculate the average clicks per UserID on a weekly basis.

Here's how you can do it:

1. Create a new calculated column in your table to extract the week number from the "Week ending date" column:

WeekNumber = WEEKNUM('YourTable'[Week ending date])

Create a measure to calculate the total clicks per UserID:

TotalClicks = SUM('YourTable'[View on Pages])

Create another measure to calculate the average clicks per UserID per week:

AverageClicksPerWeek =
AVERAGEX(
SUMMARIZE(
'YourTable',
'YourTable'[User ID],
'YourTable'[WeekNumber],
"TotalClicks", [TotalClicks]
),
[TotalClicks]
)

This measure calculates the total clicks per UserID for each week, then calculates the average of those totals across all weeks.

Once you have created these measures, you can add them to your visualizations to display the average clicks per UserID on a weekly basis.

Make sure to replace 'YourTable' with the name of your table in Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Thank you so much, very appreciated!