Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi @cingberg ,
Based on your description, you can do some steps as follows.
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.
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
Yuna,
I think he meant the output be like something in excel below.
Thanks,
-RK
Hi @cingberg ,
Based on your description, you can do some steps as follows.
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.
@v-yuaj-msft Thanks Yuna, thanks for the wonderful solution. Although I have few questions:
1. I didn't understand why you had to create 2 virtual tables, couldn't it be done with one?
2. Is there any way to do this without creating virtual tables and with measures?
3. As this doesn't talk to rest of the model then it gets little complicated. Is there a way to integrate it along?
Thanks,
-rk
Hi @Ritesh_Air ,
Based on the relationship between the tables of sample data you gave, I provide the above method. The measure is not necessary, but some processes are important and cannot be omitted. I am looking forward to whether someone else has a better solution.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RK - yes, that is the output I'm looking for.
Hi Yuna,
In addition, I need a column outlining # of weeks for that given scenario (example: for 0-20% virtual, average case sales for selected DC & Item # was 262.01cs and # of weeks within this scenario was ___ weeks). Does that make sense?
@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |