Across then Down 02-27-2018 09:22 AM - last edited 06-27-2018 13:15 PM

[Greg_Deckler]

Recreates the Tableau calculation Across then Down as found in this thread:

https://community.powerbi.com/t5/Desktop/Difference-Accross-Then-Down-DAX-Formula/m-p/366101#M165610

Imagine you have some data like this:

Col A       Col B      Value

A	a	5
B	a	1
B	b	10
C	b	1
C	a	1
C	c	15
D	d	20
E	e	25
F	f	30
G	g	35
H	h	40
I	i	45
J	j	50
K	k	55
L	l	60
A	b	7

A measure can be created like this to create the Across then Down calculation.

Across Then Down = 
VAR MyCode = UNICODE(MAX(Table1[Col B]))*1000 + UNICODE(MAX(Table1[Col A]))
VAR MyTable = ADDCOLUMNS(ALL(Table1),"MyColA",[Col A],"MyColB",[Col B],"MyValue",[Value],"Rank",UNICODE([Col B]) * 1000 + UNICODE([Col A]))
VAR Previous = MAXX(FILTER(MyTable,[Rank]<MyCode),[Rank])
VAR Result = MAX(Table1[Value]) - MAXX(FILTER(MyTable,[Rank]=Previous),[MyValue])
RETURN Result

When this measure is placed into a matrix with [Col B] as the rows and the [Col A] as the columns you get the result depicted. This particular measure does subtraction but this could be easily modified to do addition, multiplication, division, etc.

eyJrIjoiNGFlOTkzMTMtZTRhMi00NDkyLTkzN2QtYmE4MGQ0Yjc5Zjc0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9