cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cingberg
Regular Visitor

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:

 

image.png

 

What I'd like to show is average sales / count of weeks for the following % virtual ranges:

 

image.png

The calculation needs to be dynamic as the model will be filtered by distribution center & item code.  My data tables are as follows:

 

image.png

 

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!

1 ACCEPTED SOLUTION

Hi @cingberg  ,

 

Based on your description, you can do some steps as follows.

  1. 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.

v-yuaj-msft_0-1611124347405.png

 

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”.

v-yuaj-msft_1-1611124409615.png

3. Create a table visual.  

v-yuaj-msft_2-1611124409618.png

 

Result:

v-yuaj-msft_3-1611124440129.png

 

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.

 

View solution in original post

10 REPLIES 10
v-yuaj-msft
Community Support
Community Support

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.

v-yuaj-msft_0-1611050150321.png

Best Regards,

Yuna

@v-yuaj-msft @cingberg 

 

Yuna,

 

I think he meant the output be like something in excel below.

 

Ritesh_Air_0-1611077962680.png

Thanks,

-RK

Hi @cingberg  ,

 

Based on your description, you can do some steps as follows.

  1. 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.

v-yuaj-msft_0-1611124347405.png

 

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”.

v-yuaj-msft_1-1611124409615.png

3. Create a table visual.  

v-yuaj-msft_2-1611124409618.png

 

Result:

v-yuaj-msft_3-1611124440129.png

 

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.

 

View solution in original post

@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?

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors