Forum Discussion
Resolver IIDifference (Accross Then Down)--DAX Formula
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.
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
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
8 Replies
- Greg_Deckler
Community Champion
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.