Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to set the result in the first row as opposed to the later row. For example
Group | Value | Index |
A | 10 | 1 |
A | 5 | 2 |
A | 3 | 3 |
B | 19 | 1 |
B | 15 | 2 |
C | 15 | 1 |
C | 10 | 2 |
C | 7 | 3 |
C | 17 | 4 |
Which would look like this
Group | Value | Index | Result |
A | 10 | 1 | 5 |
A | 5 | 2 | 2 |
A | 3 | 3 | NULL |
B | 19 | 1 | 4 |
B | 15 | 2 | NULL |
C | 15 | 1 | 5 |
C | 10 | 2 | 3 |
C | 7 | 3 | -10 |
C | 17 | 4 | NULL |
note that many of the similar questions will send the result in the next row. I want it to stay in the first row.
Solved! Go to Solution.
Hi @user35131 ,
You can create a measure as below to get it, please find the details in the attachment.
Result =
VAR _selgroup =
SELECTEDVALUE ( 'Table'[Group] )
VAR _selindex =
SELECTEDVALUE ( 'Table'[Index] )
VAR _nextvalue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Group] = _selgroup
&& 'Table'[Index] = _selindex + 1
)
)
RETURN
IF ( ISBLANK ( _nextvalue ), BLANK (), SUM ( 'Table'[Value] ) - _nextvalue )
In addition, you can create a calculated column as below to get it.
Column =
VAR _nextvalue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Group] = EARLIER('Table'[Group])
&& 'Table'[Index] = EARLIER('Table'[Index]) + 1
)
)
RETURN
IF ( ISBLANK ( _nextvalue ), BLANK (), 'Table'[Value] - _nextvalue )
Best Regards
Hi,
This calculated column formula works
Column = if(ISBLANK(LOOKUPVALUE(Data[Value],Data[Index],CALCULATE(MIN(Data[Index]),FILTER(Data,Data[Group]=EARLIER(Data[Group])&&Data[Index]>EARLIER(Data[Index]))),Data[Group],Data[Group])),BLANK(),Data[Value]-LOOKUPVALUE(Data[Value],Data[Index],CALCULATE(MIN(Data[Index]),FILTER(Data,Data[Group]=EARLIER(Data[Group])&&Data[Index]>EARLIER(Data[Index]))),Data[Group],Data[Group]))
Hope this helps.
Hi @user35131 ,
You can create a measure as below to get it, please find the details in the attachment.
Result =
VAR _selgroup =
SELECTEDVALUE ( 'Table'[Group] )
VAR _selindex =
SELECTEDVALUE ( 'Table'[Index] )
VAR _nextvalue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Group] = _selgroup
&& 'Table'[Index] = _selindex + 1
)
)
RETURN
IF ( ISBLANK ( _nextvalue ), BLANK (), SUM ( 'Table'[Value] ) - _nextvalue )
In addition, you can create a calculated column as below to get it.
Column =
VAR _nextvalue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Group] = EARLIER('Table'[Group])
&& 'Table'[Index] = EARLIER('Table'[Index]) + 1
)
)
RETURN
IF ( ISBLANK ( _nextvalue ), BLANK (), 'Table'[Value] - _nextvalue )
Best Regards
@user35131 , for that we need to have some date of index column
Add an index column in the power query
Index Column: https://youtu.be/NS4esnCDqVw
Then create a new column in dax like
diff = [result] - max(filter(Table, [group] = earlier([group] ) && [Index] = earlier([index]) +1 ), [result])
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
You have result in the formula before it is defined. Will this work? The second table is the table I want. The first table is the one I have.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |