cancel
Showing results for 
Search instead for 
Did you mean: 

Across then Down

Super User
352 Views
Highlighted
Super User
Super User

Across then Down

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.

 

 

 

 

 

 

 

 

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!