Forum Discussion
Dynamic Average Sales by Week by Specified Measure Range
Hi There,
Looking for some assistance w/dynamic average sales calculation for a specified range of measure results. The model is intended to showcase relationship between K12 Students in Virtual Settings only (expressed as a percentage) vs. average weekly sales by item / distribution center.
I've successfully created the following visual:
What I'd like to show is average sales / count of weeks for the following % virtual ranges:
The calculation needs to be dynamic as the model will be filtered by distribution center & item code. My data tables are as follows:
I think the solution would be aggregated table that showcases Week Start Date, DC Code, Item Code, and summarized enrollment / summarized virtual students by week - but I've been unsuccessful in creating. Any help would be greatly appreciated - thanks!
Hi cingberg ,
Based on your description, you can do some steps as follows.
- Create some calculated tables.
Table 2 =
VAR x1 =
ADDCOLUMNS (
SUMMARIZE (
'Burbio Weekly Tracker',
'Calendar'[Week Start Date],
"Enrollment", SUM ( 'Burbio Weekly Tracker'[Student Enrollment] ),
"Student", SUM ( 'Burbio Weekly Tracker'[Students Virtual] )
),
"% virtual only", [Student] / [Enrollment]
)
RETURN
ADDCOLUMNS (
x1,
"Range Name",
IF (
[% virtual only] <= 0.2,
"0 to 20% Virtual",
IF (
[% virtual only] <= 0.4,
"20% to 40% Virtual",
IF (
[% virtual only] <= 0.6,
"40% to 60% Virtual",
IF (
[% virtual only] <= 0.8,
"60% to 80% Virtual",
IF ( [% virtual only] <= 1, "80% to 100% Virtual" )
)
)
)
)
)Table =
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'OBIQuery',
'Calendar'[Week Start Date],
OBIQuery["Product"."SKU"],
"Invoice cases", SUM ( OBIQuery[Invoice Cases] )
),
[Week Start Date] >= DATE ( 2020, 8, 10 )
&& [Week Start Date] <= DATE ( 2021, 1, 10 )
),
"% virtual",
LOOKUPVALUE (
'Table 2'[% virtual only],
'Table 2'[Week Start Date], [Week Start Date]
),
"Range",
LOOKUPVALUE (
'Table 2'[Range Name],
'Table 2'[Week Start Date], [Week Start Date]
)
)I use “Enter Data” to create a “Range” Table.
I also create a measure to count the weeks (it’s optional). You can choose not to create it.
weeks = DISTINCTCOUNT('Table'[Week Start Date])2. Create a relationship between “Range” and “Table”.
3. Create a table visual.
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
- amitchandak
Super User
cingberg , if you need a bucket on the measure you need to take an independent table approach.
Refer my Video on That https://www.youtube.com/watch?v=CuczXPj0N-k
How to deal with week
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8 Hi cingberg ,
Can you kindly share some sample data and the expected result to have a clear understanding of your question? It will help me to do some tests.
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
Best Regards,
Yuna
Hi Yuna,
Here's the PBIX link: https://drive.google.com/file/d/10S6bSnVwU12JY5EmnWwGsnr3xqNDYaLc/view?usp=sharing
Please let me know if you have issues accessing. As I've noted on the PBIX file, I essentially need to showcase two variables dynamically:
1. average weekly sales for given % opening parameter
2. # of occurances for said parameter for validity on average sales
Thanks - let me know if you have issues accessing.
Hi cingberg ,
I'm still a little confused. Is the following result what you want? If not, Please offer me a more detailed logical calculation formula and a screenshot of your desired result. It will be helping much. Thanks in advance.
Best Regards,
Yuna
