cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver II

## Difference (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.

2 ACCEPTED SOLUTIONS
Resolver II

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,

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
8 REPLIES 8
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Resolver II

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.

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

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Resolver II

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,

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Resolver II

Great..!!

Thanks a lot

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

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])```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors