Difference (Accross Then Down)--DAX Formula

[kaushikd]

Hi Guys,

Here is my sample data:-

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

When I draft it in power bi it looks like this:-

Now What I want is to find out Difference(Across row then Down By Column), So basicly the difference will be calculated left to right in a row, when row ends then it should not restart, rather take the value from previous row last column and continues the calculation. Below is the desired output.

[kaushikd · Accepted solution]

Hi @Greg_Deckler

I did the same thing but there is some mismatch, I think you are finding the difference for current value and previous MAX value, but I want Difference between Current value and Previous value----->

Here is the output what I got from your calculation, and what I need exactly

Here is the Pbix file also,

https://drive.google.com/file/d/1DqF4zUb5RYgYIiJPrBAh-7je83A6mYlz/view?usp=sharing

[Greg_Deckler]

Out of blatant curiousity, what is the use case for this or is this just a mental excercise?

[kaushikd]

Hi @Greg_Deckler

Actually there is a functioinality in Tableau(i.e Compute using-->Table Across then Down)

I need to replicate the same thing in power bi, and yes this is urgently required by the customers. 

If any solution is possible, or else any work around.

Here is the link:-

https://www.thedataschool.co.uk/megan-hunt/quick-tips-pics-2-compute-using-table-calcs/

[Greg_Deckler]

This strikes me as Tableau Troll, but sure, why not.

Create this table "RowIndex":

Row CharacterRow Index

a	1
b	2
c	3
d	4
e	5
f	6
g	7
h	8
i	9
j	10
k	11
l	12

Now this table, "ColumnIndex":

Column CharacterColumn Index

A	1
B	2
C	3
D	4
E	5
F	6
G	7
H	8
I	9
J	10
K	11
L	12

Now this column in your table:

Column = LOOKUPVALUE(RowIndex[Row Index],RowIndex[Row Character],[Col B]) * 10 + LOOKUPVALUE(ColumnIndex[Column Index],ColumnIndex[Column Character],[Col A])

Now this:

Column 2 = [Value]-MAXX(FILTER(Table1,Table1[Column]<EARLIER([Column])),[Value])

Again, saying it's a Tableau thing doesn't explain the actual use case for this.

[kaushikd · Accepted solution]

Hi @Greg_Deckler

I did the same thing but there is some mismatch, I think you are finding the difference for current value and previous MAX value, but I want Difference between Current value and Previous value----->

Here is the output what I got from your calculation, and what I need exactly

Here is the Pbix file also,

https://drive.google.com/file/d/1DqF4zUb5RYgYIiJPrBAh-7je83A6mYlz/view?usp=sharing

[Greg_Deckler · Accepted solution]

@kaushikd- I created a measure for this that does away with all of the intermediary tables, columns, etc. I put it in the Quick Measure gallery here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Across-then-Down/m-p/366354#M108

[kaushikd]

Great..!!

It worked fine...this made me your fan...

Thanks a lot

[Greg_Deckler]

@kaushikd- Glad we got there. That's an odd calculation can you tell me the business context in which this is used? Is this something dealing with HR, manufacturing, finance? Just curious, I haven't come across that particular calculation before.

[Greg_Deckler]

Yeah, I jacked that up a little bit. Try this:

Column = LOOKUPVALUE(RowIndex[Row Index],RowIndex[Row Character],[Col B]) * 10 + LOOKUPVALUE(ColumnIndex[Column Index],ColumnIndex[Column Character],[Col A])

Column 2 = MAXX(FILTER(Table1,Table1[Column]<EARLIER([Column])),[Column])

Column 3 = [Value]-LOOKUPVALUE([Value],[Column],[Column 2])