MAX-Formula

[Tito]

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:

 

[Tito · Accepted solution]

Hello,

I have solved the problem with the following DAX function.

Thank you very much for your support. 

[Dangar332]

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]

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

[Dangar332]

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]

Thank you for your help.

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

 

[Dangar332]

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.

[Dangar332]

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]

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.

 

[gmsamborn]

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]

Thank you for your answer.

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

Thank you

[Dangar332]

Hi, @Tito 

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