Forum Discussion

Tito's avatar
Tito
Helper IV
2 years ago
Solved

MAX-Formula

Hello, I have a problem with the MAX formula. In my example, I want to calculate a sum of sales with maximum of date only for group A. And for group B I calculate the sum of sales without the maximu...
Need Help
  • Tito's avatar
    Tito
    2 years ago

    Hello,

    I have solved the problem with the following DAX function.

    Thank you very much for your support. 

Tito's avatar
Tito
Helper IV
2 years ago

Thank you for your answer.
I have tried the formula, but the calculation is not correct.

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

hi, Tito 

 

try below code 

sum sales current 2 = 
var a =  CALCULATE(
            MAX(tebelle1[sales]),
            ALLEXCEPT(tebelle1,tebelle1[date]),
            KEEPFILTERS(tebelle1[group]="a")
                   )

var b = CALCULATE(
            sum(tebelle1[sales]),
            tebelle1[sales]=a
                  )

var c = CALCULATE(
            sum(tebelle1[sales]),
             KEEPFILTERS(tebelle1[group]="b")
                  )

return 
  b+c

 

 

  • Tito's avatar
    Tito
    Helper IV
    2 years ago

    Thank you for your help.

    But when I add the date to the matrix, it doesn't show correctly.

     

    • gmsamborn's avatar
      gmsamborn
      Super User
      2 years ago

      Hi Tito 

      Would something like this help?

      Sum Sales current 2 = 
VAR MaxSalesGroupA =
    CALCULATE (
        SUM ( Tabelle1[Sales] ),
        Tabelle1[Date] = MAX ( Tabelle1[Date] ),
        KEEPFILTERS ( Tabelle1 ),
        Tabelle1[Group] = "A"
    )
VAR SalesGroupB =
    CALCULATE (
        SUM ( Tabelle1[Sales] ),
        KEEPFILTERS ( Tabelle1 ),
        Tabelle1[Group] = "B"
    )
RETURN
    MaxSalesGroupA + SalesGroupB

      Tito --- MAX formula.pbix

      • Tito's avatar
        Tito
        Helper IV
        2 years ago

        Thank you for your answer.

        I have tried this before, but it did not work.

        Thank you

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      Hi, Tito 

       

      try below 

       

       

       

      result = 
var a = CALCULATE(MAX(tebelle1[date]),KEEPFILTERS(tebelle1[group]="a"),ALLEXCEPT(tebelle1,tebelle1[group]))
var b = CALCULATE(SUM(tebelle1[sales]),FILTER(tebelle1,tebelle1[date]=a))
var c = CALCULATE(SUM(tebelle1[sales]),KEEPFILTERS(tebelle1[group]="b"))
return 
b+c

       

       

       

       

       

       

      • Tito's avatar
        Tito
        Helper IV
        2 years ago

        Thanks for the further help.

        When I select "A" or "B" from the filter, the correct result comes up. But if I do not use the filters, the correct result does not come.

         

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      hi, Tito 

       

      if you want only those name which have sales with max date and want to show only max date and name related to those date then change order of column in visual

       

      like below  with same result measure.