DAX MAX for you HACKS :)

[musicbydannyd]

Probably a simple answer for you DAX wizards, but I'm stuck on a formula.

I'm importing some Point of Sale data from a SQL server 'view' which joins 2 tables... 1 with a summary values for each receipt and another with the item details. Unfortunately, one of the values I'd like to summarize is only in the summary table and is repeated for each line/row of item detail. I need one value for each unique ticket #. I've tried various combinations of CALULATE, DISTINCT, MAX, FILTER, GROUPBY, etc. with no luck so far... I'm sure I'm missing something obvious. Thanks SO MUCH in advance.

[KHorseman · Accepted solution]

Total Diners = IF(
	HASONEVALUE(ReceiptTable[Receipt #]),
	MAX(ReceiptTable[# of Diners]),
	SUMX(
		VALUES(ReceiptTable[Receipt #]),
		CALCULATE(MAX(ReceiptTable[# of Diners]))
	)
)

Technically only the SUMX part is necessary but this will run faster if you want to show individual receipts with a total at the bottom.

[musicbydannyd]

MANY THANKS to @KHorseman and @v-haibl-msft... both solutions worked like a charm!

[Greg_Deckler]

Agreed, would need some sample or example data.

[Vvelarde]

@musicbydannyd

What do you need to do. Show us a sample.

Alos do yu need a table with items and the value. A graph?

[musicbydannyd]

Vvelarde, thanks for the reply. Below is an example (hopefully the table copied over correctly) of the raw data I'm pulling. I'd like to be able to create a measure to sum the "# of Diners" for each unique "Receipt #"... in this case it would be a total of 6, not 24. Hopefully this makes sense.

Company

Div Code

Division

Outlet

Receipt #

Date

Shift

Clerk

Table #

# of Diners

Patron Code

Patron Type

Patron Last

Patron First

Add1

Add2

City, State

Add3

Add4

Zip

Gender

BirthDate

ResignDate

Time

Line #

Group Code

Group

Cat Code

Category

Item Code

Item

Item $

Units

1

F

F&B

Grille

23003536

10/14/2015

1

John Doe

4

G1

Guest

Claus

Santa

1234 Main St

North Pole, AK

99999

M

12/25/1900

01/01/1900

5:57 PM

1

D01

Beverages

ZO01

Other Drinks

PA005

Powerade Berry

2

1

1

F

F&B

Grille

23003536

10/14/2015

1

John Doe

4

G1

Guest

Claus

Santa

1234 Main St

North Pole, AK

99999

M

12/25/1900

01/01/1900

5:57 PM

2

B01

Beer

ZB01

Beer

CL002

Coors Light Can

18

6

1

F

F&B

Grille

23003536

10/14/2015

1

John Doe

4

G1

Guest

Claus

Santa

1234 Main St

North Pole, AK

99999

M

12/25/1900

01/01/1900

5:57 PM

3

B01

Beer

ZB01

Beer

HN002

Heineken Can

24

6

1

F

F&B

Grille

23003536

10/14/2015

1

John Doe

4

G1

Guest

Claus

Santa

1234 Main St

North Pole, AK

99999

M

12/25/1900

01/01/1900

5:58 PM

4

F01

Food

ZS01

Snacks

MM002

M&M peanut

1

1

1

F

F&B

Grille

23003536

10/14/2015

1

John Doe

4

G1

Guest

Claus

Santa

1234 Main St

North Pole, AK

99999

M

12/25/1900

01/01/1900

5:58 PM

5

F01

Food

ZS01

Snacks

PP001

Planters Salted Peanuts

1

1

2

R

Retail

Gift Shop

23003537

10/14/2015

1

Jane Doe

0

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

5:59 PM

1

P01

Products

ZI02

Impulse Items

DB009

Decorative Golf Ball

9

2

2

R

Retail

Gift Shop

23003537

10/14/2015

1

Jane Doe

0

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

5:59 PM

2

P01

Products

ZZ01

Misc Food

WG001

Wrigley's Spmt 5pk

1

1

8

S

Spa

Relax Spa

23003540

10/14/2015

1

Joe Blow

0

M2

Member

Tine

Kris

4321 Park Ave

Anywherein, NY

12345

F

03/05/1951

01/01/1900

6:00 PM

1

F10

Services

ZS10

Spa Services

F1001

Facial 1hr

90

1

1

F

F&B

Bar

23003541

10/14/2015

1

John Doe

2

M2

Member

Raphone

Mike

1600 Pennsylvania Ave NW

Washington, DC

20500

M

01/01/1900

01/01/1900

6:02 PM

1

W02

Wine

ZW02

White Wine

TB002

2 Buck Chuck Pinot Grigio

2

1

1

F

F&B

Bar

23003541

10/14/2015

1

John Doe

2

M2

Member

Raphone

Mike

1600 Pennsylvania Ave NW

Washington, DC

20500

M

01/01/1900

01/01/1900

6:02 PM

2

W02

Wine

ZR05

Champagne

CH021

Champipple

0.75

1

Thanks again,

DannyD

[v-haibl-msft · Accepted solution]

@musicbydannyd

The solution provided by KHorseman should be correct, you can also try with this one.

Measure = 
SUMX (
    VALUES ( Table1[Receipt #] ),
    FIRSTNONBLANK ( Table1[# of Diners], Table1[# of Diners] )
)

Best Regards,

Herbert

[KHorseman]

Yeah, MAX is only one of several formulas that would all work identically here. I only stuck with it because it was the first thing @musicbydannyd tried. Since all values for that column are the same, MAX, MIN, AVERAGE, and FIRSTNONBLANK would all return the same value. SUM is the only common aggregator that wouldn't work in this case.

[KHorseman · Accepted solution]

Total Diners = IF(
	HASONEVALUE(ReceiptTable[Receipt #]),
	MAX(ReceiptTable[# of Diners]),
	SUMX(
		VALUES(ReceiptTable[Receipt #]),
		CALCULATE(MAX(ReceiptTable[# of Diners]))
	)
)

Technically only the SUMX part is necessary but this will run faster if you want to show individual receipts with a total at the bottom.