Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Solved! Go to Solution.
Hello,
I have solved the problem with the following DAX function.
Thank you very much for your support.
Hello,
I have solved the problem with the following DAX function.
Thank you very much for your support.
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
Thank you for your answer.
I have tried the formula, but the calculation is not correct.
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
Thank you for your help.
But when I add the date to the matrix, it doesn't show correctly.
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.
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
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.
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
Thank you for your answer.
I have tried this before, but it did not work.
Thank you
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
92 | |
82 | |
63 | |
62 | |
58 |
User | Count |
---|---|
159 | |
115 | |
103 | |
75 | |
66 |