Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am trying to calculate average weekly sales of the current fiscal quarter so that I can use those results to estimate what the quarterly results will be. I think I am making this more difficult that what it needs to be, but am coming up at a loss to figure this out.
In excel, I could go to the formula and simply divide out by the number of the current fiscal week to get the average, but I want to be able to do that automatically within Power BI using a dax formula.
Below is a screen shot of the current numbers. And so for quarter 3, I want to take the total ($1,804,215 so far) and divide that by the number of fiscal weeks (5) to get my average. Easy enough to do with a formula, but when fiscal week 32 rolls around, I want to have the same formula calulate the average. I would then want that average to be in its own column so that it can represent the pace for that quarter. When the 4th quarter starts, I would need the process to start over.
Thanks in advance
Solved! Go to Solution.
Hi,
I am not sure how your caledar table looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a solution for your dataset.
Avg quarterly: =
AVERAGEX (
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'Calendar', 'Calendar'[Fiscal Quarter], 'Calendar'[Fiscal Week] ),
ALLEXCEPT ( 'Calendar', 'Calendar'[Fiscal Quarter] )
),
"@sales", [Sales measure:]
),
[@sales]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hello, @Jihwan_Kim . See below screen shot. I was able to reach out to one of our developers who solved this so I will mark this as solved. For your reference, below are the DAX measurements they created for this.
Weekly Avg
Quarter Pace
Thanks
Hello, @Jihwan_Kim . See below screen shot. I was able to reach out to one of our developers who solved this so I will mark this as solved. For your reference, below are the DAX measurements they created for this.
Weekly Avg
Quarter Pace
Thanks
Hi,
I am not sure how your caledar table looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a solution for your dataset.
Avg quarterly: =
AVERAGEX (
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'Calendar', 'Calendar'[Fiscal Quarter], 'Calendar'[Fiscal Week] ),
ALLEXCEPT ( 'Calendar', 'Calendar'[Fiscal Quarter] )
),
"@sales", [Sales measure:]
),
[@sales]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Hello. I present the solution you provided and the results, but was informed I needed to carry forward the average value through the following quarter. So in my original request where I stated "I would then want that average to be in its own column so that it can represent the pace for that quarter. When the 4th quarter starts, I would need the process to start over..." I was wrong in that the value would need to start over. I tried to work through the calculations you provided, but am not getting it to work. Could you take a look for me and provide a new solution?
Thanks
Hi,
Sorry that I do not understand. Could you please provide a sample pbix file's link with Q4 data included and also with how the expected numbers/visualization look like? And then I can try to look into it to come up with a more accurate solution.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hello and sorry for the confusion.
So the previous solution you provided allowed me to create a measure called Weekly Avg where I am able to estimate future weeks based on actuals, but it currently only calculates for the current Fiscal Quarter. I need to carry those values forward to the next Fiscal Quarter (see screen shot). From that I can easily calculate a value for the Quarterly Pace column.
Weekly Avg Formula:
The other column, Cumulative Net Sales, which may be impacting the results is calculated by:
Please let me know if more information is needed.
Thanks
Hi,
Thank you for your explanation.
However, in your screen capture, Quart-4 NetSales is blank. May I ask how do you want to see this as a weekly average?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |