Calculate average per weekday and customer

[Sail_v]

Hi! 

I am new to Power BI. I’m trying to figure out how to create a new measure.

I would like to calculate the total number of units sold for a specific date and customer as a percentage of the average for the same customer and weekday (e.g. Monday). 

My data set contains several transactions each day for the same customer. 

I have created a date table in addition to my dataset.

The data look something like this:

Transaktion ID	Date	Weekday	Customer	Units sold
2	8/9/2022	3	A	80
3	8/9/2022	3	A	20
4	8/9/2022	3	A	20
1	8/9/2022	3	B	10
28	8/16/2022	3	A	80
…

And I would like to create a visual with the following information:

Date	Weekday	Customer	Total units sold	Average units sold per weekday and customer	Total units sold as a percentage of average
8/9/2022	3	A	120	100	120%
…

It would also be nice to limit the calculated average to a date in period e.g. 2 months from last transaction.

Any tips?

[daXtreme · Accepted solution]

Please find a file with a solution attached...

[amitchandak]

@Sail_v ,

calculate(Averagex(values(Table[Date]), calculate(Sum(Table[Units sold])) ), filter(allselected(Table), Table[Customer] = max(Table[Customer])  && Table[Weekday] = Max(Table[Weekday]) ))

Better to have week day column = Weekday([Date])

if you use date table,

calculate(Averagex(values(Table[Date]), calculate(Sum(Table[Units sold])) ), filter(allselected(Date),   Date[Weekday] = Max(Date[Weekday]) ))