MAX function in DAX for Power Pivot

Anything related to PowerPivot and DAX Formuale
DAX_Beginner
Posts: 2
Joined: Thu Apr 08, 2021 5:43 pm

MAX function in DAX for Power Pivot

Postby DAX_Beginner » Thu Apr 08, 2021 6:05 pm

Hi all

I am very new to Power Pivot and DAX as a requirement for work and was hoping I could get some help on an issue that I am facing!
Tried putting an attachment but I can't seem to do it so I'll try my best to describe my data here

So I have a table called [Data] with 3 columns:
1) Type (either A, B, or C)
2) Item R: numerical value
3) Item S: numerical value

And it looks something like this:
Type | Item R | Item S
1 | 5 |6
1 | 4 |7
2 | 8 |5
2 | 5 |6

The idea is to take the maximum of Item R or Item S for each type, and hence I need to put it into a power pivot such that I get the following result:
Type | Item R | Item S | Result
1 |9 |13 |13
2 |13 |11 |13
Total |22 |24 |****

My issue is that I need **** to be 26 but I am currently getting 24
Which I believe is because the MAX function is being applied on the aggregate.

I am using 3 DAX measures right now:
1) Total Item R = SUM(Data[Item R])
2) Total Item S = SUM(Data[Item S])
3) Result = MAX([Total Item R],[Total Item S])

Hope someone will be able to enlighten me!

Thank you! :)

rwfigtree
Posts: 35
Joined: Tue Jan 05, 2021 8:49 pm
Location: Sydney, Australia

Re: MAX function in DAX for Power Pivot

Postby rwfigtree » Thu Apr 08, 2021 8:13 pm

i couldn't attach file either, weird.

measure1:=SUM(Table1[ItemR])
measure2:=SUM(Table1[ItemS])
measure3:=var x = SUMMARIZE(Table1,Table1[Type],"XX",max(SUM(Table1[ItemR]),sum(Table1[ItemS])))
return SUMX(x,[XX])

DAX_Beginner
Posts: 2
Joined: Thu Apr 08, 2021 5:43 pm

Re: MAX function in DAX for Power Pivot

Postby DAX_Beginner » Fri Apr 09, 2021 2:04 pm

That worked perfectly. Thank you! :)


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 5 guests