Forum Discussion

Sam-Ke's avatar
Sam-Ke
New Member
9 months ago
Solved

Calculate Max volumes at Week level

Hello Everyone,   I am sure similar question was already answered, but i might not have the correct way to formulate my research to find it ğŸ˜…   I want to calculate the maximum amount of volumes ...
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 months ago

    Hi Sam-Ke,

    Try one of these solutions :

     

    First One : Create Weekly Aggregated Measures First

    Factory Past Deliveries Weekly = 
CALCULATE(
    [Factory Past Deliveries],
    ALLEXCEPT('Calendar', 'Calendar'[WeekNum])
)

Replenishment Plan Factory Weekly = 
CALCULATE(
    [Replenishment Plan Factory],
    ALLEXCEPT('Calendar', 'Calendar'[WeekNum])
)

Max Replenishment / SP =
MAXX(
    VALUES('Calendar'[WeekNum]),
    MAX([Factory Past Deliveries Weekly], [Replenishment Plan Factory Weekly])
)

     

    Second Approach: Direct with SUMMARIZE

    Max Replenishment / SP =
MAXX(
    SUMMARIZE(
        'Calendar',
        'Calendar'[WeekNum],
        "WeeklyMax",
        MAX(
            CALCULATE([Factory Past Deliveries], ALLEXCEPT('Calendar', 'Calendar'[WeekNum])),
            CALCULATE([Replenishment Plan Factory], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        )
    ),
    [WeeklyMax]
)

     

    Third Approach: with ADDCOLUMNS

    Max Replenishment / SP =
MAXX(
    ADDCOLUMNS(
        VALUES('Calendar'[WeekNum]),
        "WeeklyValue",
        VAR PastDeliveries = CALCULATE([Factory Past Deliveries], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        VAR Replenishment = CALCULATE([Replenishment Plan Factory], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        RETURN
            MAX(PastDeliveries, Replenishment)
    ),
    [WeeklyValue]
)

     

    Bonus Tip : 

    If you are using this for setting the Y axis maximum in a visual you might want to add a buffer:

    Max Replenishment / SP for Axis =
[Max Replenishment / SP] * 1.1  // Adds 10% buffer for better visualization

     

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
Ahmed-Elfeel's avatar
Ahmed-Elfeel
Icon for Super User rankSuper User
9 months ago

Hi Sam-Ke,

Try one of these solutions :

 

First One : Create Weekly Aggregated Measures First

Factory Past Deliveries Weekly = 
CALCULATE(
    [Factory Past Deliveries],
    ALLEXCEPT('Calendar', 'Calendar'[WeekNum])
)

Replenishment Plan Factory Weekly = 
CALCULATE(
    [Replenishment Plan Factory],
    ALLEXCEPT('Calendar', 'Calendar'[WeekNum])
)

Max Replenishment / SP =
MAXX(
    VALUES('Calendar'[WeekNum]),
    MAX([Factory Past Deliveries Weekly], [Replenishment Plan Factory Weekly])
)

 

Second Approach: Direct with SUMMARIZE

Max Replenishment / SP =
MAXX(
    SUMMARIZE(
        'Calendar',
        'Calendar'[WeekNum],
        "WeeklyMax",
        MAX(
            CALCULATE([Factory Past Deliveries], ALLEXCEPT('Calendar', 'Calendar'[WeekNum])),
            CALCULATE([Replenishment Plan Factory], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        )
    ),
    [WeeklyMax]
)

 

Third Approach: with ADDCOLUMNS

Max Replenishment / SP =
MAXX(
    ADDCOLUMNS(
        VALUES('Calendar'[WeekNum]),
        "WeeklyValue",
        VAR PastDeliveries = CALCULATE([Factory Past Deliveries], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        VAR Replenishment = CALCULATE([Replenishment Plan Factory], ALLEXCEPT('Calendar', 'Calendar'[WeekNum]))
        RETURN
            MAX(PastDeliveries, Replenishment)
    ),
    [WeeklyValue]
)

 

Bonus Tip : 

If you are using this for setting the Y axis maximum in a visual you might want to add a buffer:

Max Replenishment / SP for Axis =
[Max Replenishment / SP] * 1.1  // Adds 10% buffer for better visualization

 

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • Sam-Ke's avatar
    Sam-Ke
    New Member
    9 months ago

    Hello Ahmed,

     

    thanks for taking so much time and offer so many options!

     

    i tried the 3 options. They all returned me indeed the same max amount on a week number. However it isn't sensitive anymor to the Year filter i have : whatever the year selected it sums all available volumes (illustration below - sorry different Names, but same data).

     

    I have the column Week-Year available, so i tried to use it : it returned be the correct value with your option 2 & 3 and worked fine 🙂

     

    thanks for the bonus Tip!

     

    With [WeekNum]

     

     

     

    • Ahmed-Elfeel's avatar
      Ahmed-Elfeel
      Icon for Super User rankSuper User
      9 months ago

      Hi Sam-Ke,

       

      Great to hear the solution worked! If this resolved your problem, could you mark it as a solution?

       

      That way it can help others with similar questions.

      If you need any more help with this or have another question, just let me know happy to assist further! 😊