Forum Discussion

asimpleman9to5's avatar
asimpleman9to5
Frequent Visitor
4 years ago
Solved

Difference between rows

Hi all. 

 

First Photo

 

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. 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating measures.

     

     

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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating measures.

     

     

    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 )
    )
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

     

    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