Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kaushikd
Resolver II
Resolver II

Difference (Accross Then Down)--DAX Formula

Hi Guys,

 

Here is my sample data:-

Col ACol BValue
Aa5
Ba1
Bb10
Cb1
Ca1
Cc15
Dd20
Ee25
Ff30
Gg35
Hh40
Ii45
Jj50
Kk55
Ll60
Ab7

 

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

Capture.JPG

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.

Untitled.png

 

 

2 ACCEPTED SOLUTIONS

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

 

Capture.JPG

View solution in original post

@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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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/

 

Capture.JPG

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

 

Create this table "RowIndex":

Row CharacterRow Index

a1
b2
c3
d4
e5
f6
g7
h8
i9
j10
k11
l12

 

 

Now this table, "ColumnIndex":

 

 

 

Column CharacterColumn Index

A1
B2
C3
D4
E5
F6
G7
H8
I9
J10
K11
L12

 

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.

 

 

 

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

 

Capture.JPG

@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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Great..!!

 

It worked fine...this made me your fan... Smiley Very Happy

 

Thanks a lot

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.