Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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
Out of blatant curiousity, what is the use case for this or is this just a mental excercise?
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/
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.
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
Great..!!
It worked fine...this made me your fan...
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.
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])
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
98 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
120 | |
73 | |
72 | |
63 |