Forum Discussion

yellow43's avatar
yellow43
Helper I
3 years ago
Solved

Average per week by trimester

Hi Community,

 

Can you help me calculating average per week by trimester.

This is a sample of my data:

 

Week Agent Measure [Sales]
1 A 0
1 B 0
1 C 0
1 D 0
2 A 3
2 B 3
2 C 3
2 D 0
3 A 4
3 B 5
3 C 5
3 D 5
4 A 83
4 B 47
4 C 81
4 D 102
5 A 101
5 B 147
5 C 98
5 D 94
6 A 101
6 B 53
6 C 90
6 D 67
7 A 48
7 B 55

 

I would like to calculate average of Sales (this is a measure SUM of Sales by day by agent) per week but by trimester.

Another issue, on the 1st trimester I would like to exclude from week 1 to week 3.

 

Can you help me?

Thank

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi yellow43 ,

    According to your description, here's my solution. 

    Sample:

    Table_Calendar contains two columns: Trimester and Week.

    The fact table is the same with you provided. The two tables are related with Week column.

    1.Create a Product table as below, sort the Product column by index column. Don't create relationship between this table with others.

    2.Create a measure:

    Measure =
IF (
    MAX ( 'Product'[Product] ) = "Average per product",
    IF (
        ISINSCOPE ( 'Table'[Week] ),
        AVERAGEX (
            FILTER ( ALL ( 'Table' ), 'Table'[Week] = MAX ( 'Table'[Week] ) ),
            'Table'[Measures[Sales]]]
        ),
        SUM ( 'Table'[Measures[Sales]]] ) / COUNT ( Table_Calendar[Trimester] )
    ),
    IF (
        ISINSCOPE ( 'Table'[Week] ),
        MAXX (
            FILTER ( 'Table', 'Table'[Agent] = MAX ( 'Product'[Product] ) ),
            'Table'[Measures[Sales]]]
        ),
        SUMX (
            FILTER ( 'Table', 'Table'[Agent] = MAX ( 'Product'[Product] ) ),
            'Table'[Measures[Sales]]]
        )
            / COUNT ( Table_Calendar[Trimester] )
    )
)

    In a matrix visual, get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi yellow43 ,

    Sorry I'm not quite clear about the calculation logic, could you please explain more or show the expected reault?

     

    Best regards,

    Community Support Team_yanjiang

  • yellow43's avatar
    yellow43
    Helper I
    3 years ago

    Hello v-yanjiang-msft , 

     

    This is the expected output (highlighted in yellow): 

     

    Output from 1st trimester

     

     Note: on 1st trimester exclude from week 1-3

     

    Output 2nd trimester

    Can you help me?

    Thank you!

  • yellow43's avatar
    yellow43
    Helper I
    3 years ago

    Hi v-yanjiang-msft ,

     

    I would like to know average per week on the trimester.

    For instance, 2nd trimester I have following: 

    Total Sales = 2304 units

    Nr weeks 2nd trimester = 13

    Average per week = 2304/13 = 177,23 units (2nd trimester)

     

    Let me tell you that on my Table_Calendar I have column with Trimester (number) and column with Week (number)

     

    Thank you!

    Is it more clear?

     

     

     

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      3 years ago

      Hi yellow43 ,

      According to your description, here's my solution. 

      Sample:

      Table_Calendar contains two columns: Trimester and Week.

      The fact table is the same with you provided. The two tables are related with Week column.

      1.Create a Product table as below, sort the Product column by index column. Don't create relationship between this table with others.

      2.Create a measure:

      Measure =
IF (
    MAX ( 'Product'[Product] ) = "Average per product",
    IF (
        ISINSCOPE ( 'Table'[Week] ),
        AVERAGEX (
            FILTER ( ALL ( 'Table' ), 'Table'[Week] = MAX ( 'Table'[Week] ) ),
            'Table'[Measures[Sales]]]
        ),
        SUM ( 'Table'[Measures[Sales]]] ) / COUNT ( Table_Calendar[Trimester] )
    ),
    IF (
        ISINSCOPE ( 'Table'[Week] ),
        MAXX (
            FILTER ( 'Table', 'Table'[Agent] = MAX ( 'Product'[Product] ) ),
            'Table'[Measures[Sales]]]
        ),
        SUMX (
            FILTER ( 'Table', 'Table'[Agent] = MAX ( 'Product'[Product] ) ),
            'Table'[Measures[Sales]]]
        )
            / COUNT ( Table_Calendar[Trimester] )
    )
)

      In a matrix visual, get the correct result:

      I attach my sample below for your reference.

       

      Best regards,

      Community Support Team_yanjiang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.