Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Sum of MAX

    Posted May 11, 2022 10:11 AM
    Edited by SAHLER Marine May 12, 2022 11:07 AM
      |   view attached
    SOLVED :

    Hi Everyone, I'm new here...

    My problem is quite simple :
    I would like a Sum of Max passengers on bord for a Class and a Date... It's OK with the following formula, but it cannot calculate the sum for one flight !! It give me the MAX of the class it find...


    Formula :
    NB_PAX = CALCULATE (MAX(Conso_CatSpec[Pax] ), DISTINCT(Conso_CatSpec[Flight]), DISTINCT(Conso_CatSpec[Dep.Date]))

    See attached files for an exemple

    Many thanks for your help :)
    Marine

    ------------------------------
    SAHLER Marine
    ------------------------------


  • 2.  RE: Sum of MAX

    Silver Contributor
    Posted May 12, 2022 03:24 AM
    Hi,

    You will need the SUMX function to compute every row of your data, so it should be something like :

    Sum of MAX = SUMX( VALUES( [Class] ), CALCULATE( ALL( [Flights] ) , MAX ([Nb PAX]) ) )

    Class = Eco, premium...
    Flights = AF650YRGML
    NB Pax your measure counting pax

    Hope it helps

    ------------------------------
    Olivier
    ------------------------------



  • 3.  RE: Sum of MAX

    Posted May 12, 2022 08:35 AM
      |   view attached
    Hi Olivier,

    And thanks for your response.

    I've tried your solution but it says "A function MAX has been used in a True/False expression that is used as a table filter expression. This is not allowed."

    If it can help, I attached an exemple of my table.

    Thanks again,
    Marine

    ------------------------------
    SAHLER Marine
    ------------------------------

    Attachment(s)

    xlsm
    Exemple CatSpec.xlsm   72 KB 1 version


  • 4.  RE: Sum of MAX

    Posted May 12, 2022 10:48 AM
    I found something : By mixing your solution with mine, it works !

    Merci :)

    NB_PAX_Class = SUMX( VALUES( Class[Class Géné]), CALCULATE (MAX(Conso_CatSpec[Pax] ), DISTINCT(Conso_CatSpec[Flight]), DISTINCT(Conso_CatSpec[Dep.Date])))

    Many thanks,
    Marine


    ------------------------------
    SAHLER Marine
    ------------------------------



  • 5.  RE: Sum of MAX

    Silver Contributor
    Posted May 12, 2022 12:11 PM
    De rien Marine et merci d'avoir partager la solution :-)

    ------------------------------
    Olivier
    ------------------------------