cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asimpleman9to5
Frequent Visitor

Difference between rows

Hi all. 

 

asimpleman9to5_1-1660546189917.png

First Photo

 

asimpleman9to5_0-1660545587007.png

Second Photo

 

 

From the screenshots shown above, the first photo is how my data is seen now. I would require the serial numbers to be filtered and grouped together and the difference between each rows to be calculated. The last row should subsequently return a null value and the second photo is how the data should be arranged at the end. Help for this would be appreciated. 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating measures.

 

Picture1.png

 

Voltage measure: = 
IF ( HASONEVALUE ( Data[Serial No] ), SUM ( Data[Voltage] ) )

 

Row Diff measure: = 
VAR _currentvoltage = [Voltage measure:]
VAR _currentrow =
    MAX ( 'Test Type'[Index] )
VAR _nextrow =
    MINX (
        FILTER ( ALL ( 'Test Type' ), 'Test Type'[Index] > _currentrow ),
        'Test Type'[Index]
    )
VAR _nextrowvoltage =
    CALCULATE (
        [Voltage measure:],
        FILTER ( ALL ( 'Test Type' ), 'Test Type'[Index] = _nextrow )
    )
RETURN
    IF (
        HASONEVALUE ( 'Serial No'[Serial No] ),
        IF ( ISBLANK ( _nextrow ), "null", _currentvoltage - _nextrowvoltage )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
CNENFRNL
Super User
Super User

CNENFRNL_0-1660565162499.png

 

For fun only, T-sql solution

 

SELECT *
INTO #tmp
FROM
(
VALUES ('ABS1000','FUNC',3.728),('ABS2000','REC',3.899),('ABS3000','AUDIO',4.321),('ABS3000','FUNC',4.521),('ABS2000','FUNC',4.002),('ABS1000','REC',3.701),('ABS1000','AUDIO',3.752),('ABS2000','AUDIO',4.211),('ABS3000','REC',4.111)
) TB([SN],[TYPE],[VOLTAGE])

;WITH cte
AS (SELECT T.SN
         , T.TYPE
         , VOLTAGE
		 , O.[Order]
    FROM #tmp T
        JOIN
        (
            SELECT *
            FROM
            (
                VALUES
                    (1, 'FUNC')
                  , (3, 'REC')
                  , (2, 'AUDIO')
            ) T ([Order], [TYPE])
        )     AS O
            ON T.type = O.TYPE
   )
SELECT
	[SN]
	, [TYPE]
	, [VOLTAGE]
	, [VOLTAGE] - LEAD([VOLTAGE]) OVER (PARTITION BY [SN] ORDER BY [Order]) AS [Diff]
FROM cte

 

CNENFRNL_0-1660559893442.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating measures.

 

Picture1.png

 

Voltage measure: = 
IF ( HASONEVALUE ( Data[Serial No] ), SUM ( Data[Voltage] ) )

 

Row Diff measure: = 
VAR _currentvoltage = [Voltage measure:]
VAR _currentrow =
    MAX ( 'Test Type'[Index] )
VAR _nextrow =
    MINX (
        FILTER ( ALL ( 'Test Type' ), 'Test Type'[Index] > _currentrow ),
        'Test Type'[Index]
    )
VAR _nextrowvoltage =
    CALCULATE (
        [Voltage measure:],
        FILTER ( ALL ( 'Test Type' ), 'Test Type'[Index] = _nextrow )
    )
RETURN
    IF (
        HASONEVALUE ( 'Serial No'[Serial No] ),
        IF ( ISBLANK ( _nextrow ), "null", _currentvoltage - _nextrowvoltage )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you for your extensive help. I greatly appreciate it.

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.