Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

@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
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.