topic Calculate average rate of sale, year to date and vs previous year in DAX Commands and Tips

Calculate average rate of sale, year to date and vs previous year

slaterboi — Wed, 22 Nov 2023 15:34:41 GMT

Hello hello,

Very new to Power BI and trying to do what feels like quite complicated work / at least finding it hard to find solutions by googling.

I am creating a weekly sales dashboard. I have a table visual which shows EPOS sales by product, Week on week %, Year on Year %.

I also want to show Rate of Sale, average rate of sale for the year to date, and average rate of sale last year. I have calculated Rate of Sale as follows:

Rate of Sale =

DIVIDE(

CALCULATE(

SUM('Appended Retailer Data'[Value]),

Attributes[Attribute] = "EPOS Qty"),

CALCULATE(

SUM('Appended Retailer Data'[Value]),

Attributes[Attribute] = "Est. EPOS Store Count"))

This works and can be added to a table visual by product name (although not the same table visual as my WoW% and YoY% - why is that?

In theory, rate of sale year to date is just the average of the values for each week for 2023. I am not sure how to do this with my rate of sale measure.

Rate of sale for the same period last year - I think I could use the week rank column of my date table but again unsure how to make this interact with my measure.

So in summary I have 3 questions:

- How do I calculate the average of my rate of sale measure?

- How do I calculate the average of my rate of sale measure for the same period last year?

- Can these measures be shown in the same table as my EPOS data, WoW% and YoY%? If not, why not?

Any tips and advice welcome, appreciate this might not be the most complete image of my dataset and workings but it's the best I can do right now 😄

Thanks

Re: Calculate average rate of sale, year to date and vs previous year

Anonymous — Fri, 24 Nov 2023 01:26:56 GMT

Hi slaterboi ,

I'm sorry, but would you mind sharing the sample data? If you can't share more sample data, I would appreciate it if you could describe the structure of the dataset you're using (what tables are included, what columns are contained within each table, what columns are needed for your measures, etc.), that would be helpful.
And in general, you are perfectly capable of displaying multiple measures in the same table.

Best Regards,
Dino Tao

Re: Calculate average rate of sale, year to date and vs previous year

Anonymous — Fri, 24 Nov 2023 01:50:59 GMT

Hi slaterboi ,

Since I don't know what kind of data structure you are using, I can only offer you some suggestions:
You say you want to calcualte the average of the values for each week for 2023, if I understand you correctly, you want to divide Rate of Sale by the total number of weeks to calculate the average on a weekly basis. Then you need to calculate how many weeks have passed since the beginning of 2023.
If you are recording weeks in a column like the one shown below, i.e. by weeks of the year:

You can calculate how many weeks there are by using the DISTINCTCOUNT function in DAX to calculate how many different values are in the Week column.
If your weeks are recorded on a monthly basis, i.e. as shown below:

Then you need to do the math for each of the different months and add them together to get the total number of weeks.
The same goes for last year's measure.

Best Regards,

Dino Tao

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

Re: Calculate average rate of sale, year to date and vs previous year

slaterboi — Fri, 24 Nov 2023 15:03:56 GMT

Hi Dino,
Thanks for your response. I can't share the data because it's confidential but I will do my best to describe the structure. I have tables containing 2 years worth of sales data for multiple retailers with the following columns; Item number, item description, week (retailer week number), EPOS sales, EPOS quantity, average instock %, est. EPOS store count, waste value.

I have then appended these tables to get a table that includes data for all retailers (this is because the format of the data is slightly different for each retailer). I have created various measures in my appended data table: Total Sales TY, Total Sales LY, Total Sales Year on Year %, Total Sales This Week, Total Sales Last Week, Total Sales Week on Week %, Rate of Sale.

I have also have a Calendar table with the following: Week Commencing date, Retailer Week Number, Year, Calendar Week number, Week Ending date, Week Rank, Week Name, Weekday (numerical), Weekday (text)

Cheers
Matt