Forum Discussion

Tito's avatar
Tito
Helper IV
2 years ago
Solved

Calculate Sum MAX

Hello everyone,

Can someone please help me how to calculate the maximum of Sales. See the data below:

Data:


 Result:


Best regards
Tito

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    Hi Tito,

    Please try this formula:

    MAX Measure = 

VAR _Table =
     ADDCOLUMNS(
        SUMMARIZE(
            T_MaxQ,
            T_MaxQ[Name],
            T_MaxQ[ID]
        ),
        "@MaxSales",   
		VAR _MAX = CALCULATE(MAX(T_MaxQ[Date]))
		VAR _Result = 
			CALCULATE(
				SUM(T_MaxQ[Sales]),
				T_MaxQ[Date] = _MAX
				
			)
		RETURN
			_Result
    )
VAR _Result = SUMX(_Table, [@MaxSales])
RETURN
    _Result

13 Replies

  • Ajithkumar_03's avatar
    Ajithkumar_03
    Frequent Visitor
    2 years ago

    Hi Tito 

    please refer the below measures.

    Sample Table:


    Measure 1:

     

    Measure 2:


    Result:

     



    I hope this measure will help you.

    If it solves your issue, please accept it as the solution to help the other members find it more quickly.

    Best Regards,
    Ajith Kumar

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi Tito,

    Here is my solution:

    I'm using this measure:

     

    MAX Measure = 
VAR _Table =
    ADDCOLUMNS(
        SUMMARIZE(
            T_MaxQ,
            T_MaxQ[Name],
            T_MaxQ[ID]
        ),
        "@Max", CALCULATE(MAX(T_MaxQ[Sales]))
    )
VAR _Result = SUMX(_Table, [@Max])
RETURN
    _Result

     

     

     

    And the final result is this:

     

    • Tito's avatar
      Tito
      Helper IV
      2 years ago

      Hi _AAndrade  DataNinja777 
      Thank you for your reply.
      I want the sum sales by maximum of date and not the maximum of sales. See example below:

      Data:


      Result:

       

       

      • _AAndrade's avatar
        _AAndrade
        Resident Rockstar
        2 years ago

        Hi Tito,

        Please try this formula:

        MAX Measure = 

VAR _Table =
     ADDCOLUMNS(
        SUMMARIZE(
            T_MaxQ,
            T_MaxQ[Name],
            T_MaxQ[ID]
        ),
        "@MaxSales",   
		VAR _MAX = CALCULATE(MAX(T_MaxQ[Date]))
		VAR _Result = 
			CALCULATE(
				SUM(T_MaxQ[Sales]),
				T_MaxQ[Date] = _MAX
				
			)
		RETURN
			_Result
    )
VAR _Result = SUMX(_Table, [@MaxSales])
RETURN
    _Result
    • Tito's avatar
      Tito
      Helper IV
      2 years ago

      Hi DataNinja777 ,

      Thank you for your reply.
      If I select name "A", 30 should be returned. 
      I want the sum sales by maximum of date and not the maximum of sales.

      • DataNinja777's avatar
        DataNinja777
        Super User
        2 years ago

        Hi Tito 

        Sumxing over ID of the max sales measure will get your required output.  I am not sure why combining in one formula will produce the different result from separating out in two measures like below, but separating out in two measure will get your required output.

        Sumxing over ID = sumx(values('Table'[ID]),[Max sales])

        Best regards,

         

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

     

     

    HI Tito ,
    To get sales by Maximum date you can use this measure,

     

     

    SalesByMaxDate = 
CALCULATE(
    SUM('Order Data'[Sales]),
 FILTER('Order Data',
  'Order Data'[Date]
  =MAX('Order Data'[Date])
  )
  )

     

     

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    Hi Tito 
    You can try this dax

     

    Total = calculate (Sum(Table[sales]), max(Date[date])

     

    I hope I answered your question!