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 maximum. Finally I calculate the total sum (MaxSalesGroupA + SalesGroupB). In the example, if I select group A from the filter, the name "Max" has 4000, but this is not correct. In this case the name "Max" does not need to be in the matrix anymore.
I actually have a big file, but I added an example in the attachment (see picture of Excel).

Best regards


Raw data (Excel):

 

Result:

 

DAX:

 

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. 

11 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Tito 

     

    try below

    sum sales current 2 = 
var a = CALCULATE(MAX(tebelle1[date]),ALLEXCEPT(tebelle1,tebelle1[group]))
var b = SUMX(FILTER(tebelle1,tebelle1[date]=a),tebelle1[sales])
var c = SUMX(FILTER(tebelle1,tebelle1[group]="b"),tebelle1[sales])
return 
b+c
    • 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 

         

        Means You want William(5000) and Luca(5000) for group A ?

  • Tito's avatar
    Tito
    Helper IV
    2 years ago

    Hello,

    I have solved the problem with the following DAX function.

    Thank you very much for your support.