Forum Discussion

Shravan16's avatar
Shravan16
New Member
2 years ago
Solved

How to calculate a difference between 2 column.

Hello. I am trying to calculate the difference between 2 columns for the month from the matrix option on power BI but am unable to do it correctly. For example - Difference between Feb and Jan for t...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Shravan16 

     

    Please try this:

    As what you want, I create a new sample:

    The measure could be like:

    Measure 3 = 
	VAR _currentbook = SELECTEDVALUE('Table'[Book])
	VAR _currentSub = SELECTEDVALUE('Table'[Sub])
	VAR _previousMonth = MINX(
		ALL('Table'),
		MONTH('Table'[Month])
	)
	VAR _currentMonth = MONTH(MAX('Table'[Month]))
	RETURN
		IF(
			_currentMonth <> _previousMonth,
			IF(
				HASONEVALUE('Table'[Sub]),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				)
			)
		)

    The result is as follow:

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution.
Shravan16 , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it.

Then I add a measure:

MEASURE =
VAR _currentbook =
    SELECTEDVALUE ( 'Table'[Book] )
VAR _previousMonth =
    MINX ( ALL ( 'Table' ), MONTH ( 'Table'[Month] ) )
VAR _currentMonth =
    MONTH ( MAX ( 'Table'[Month] ) )
RETURN
    IF (
        _currentMonth <> _previousMonth,
        SUMX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                MONTH ( 'Table'[Month] ) = _currentMonth
                    && 'Table'[Book] = _currentbook
            ),
            'Table'[Open Nominal]
        )
            - SUMX (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    MONTH ( 'Table'[Month] ) = _previousMonth
                        && 'Table'[Book] = _currentbook
                ),
                'Table'[Open Nominal]
            )
    )

The result is as follow:

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Shravan16's avatar
Shravan16
New Member
2 years ago

Thanks Anonymous.
I have one additional question - what if the Month book A has sub-catergory - for eg Sub Category 1
                                                                                                                                           Sub Category 2
How to include the calculation of the difference for both the Book A and the sub-category. 
For eg the Book A difference is -127. When I drill down the Book A on the MAtrix , I have Sub Category 1 and 2. The expected outcome is having the difference of -127 comes from Sub-Category 1 Diff which is -120 and Sub category 2 Diff which is  -7 which leads to the total -127 for the Book A. 

Thank you for you help.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Shravan16 

     

    Please try this:
    I update this sample:

    Measure 3 = 
	VAR _currentbook = SELECTEDVALUE('Table'[Book])
	VAR _currentSub = SELECTEDVALUE('Table'[Sub])
	VAR _previousMonth = MINX(
		ALL('Table'),
		MONTH('Table'[Month])
	)
	VAR _currentMonth = MONTH(MAX('Table'[Month]))
	RETURN
		IF(
			_currentMonth <> _previousMonth,
			IF(
				HASONEVALUE('Table'[Sub]),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Sub] = _currentSub
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Sub] = _currentSub
					),
					'Table'[Open Nominal]
				),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				)
			)
		)

     The result is as follow:

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Shravan16's avatar
      Shravan16
      New Member
      2 years ago

      Hi  Anonymous, 

      It works now excepts when i have the same category in different books then the DAX does not work. 
      For example the Book A difference is -127. When I drill down the Book A on the MAtrix , I have Sub Category A1 and A2. The expected outcome is having the difference of -127 comes from Sub-Category 1 Diff which is -120 and Sub category 2 Diff which is  -7 which leads to the total -127 for the Book A.  Book B can consist of Sub Category A1 (-140) and B1 (-5) which equals to total difference of -145 for Book B. The sub Category A1 is the same product but can be in different books with different Amount. 

      Thank you.  


      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Shravan16 

         

        Please try this:

        As what you want, I create a new sample:

        The measure could be like:

        Measure 3 = 
	VAR _currentbook = SELECTEDVALUE('Table'[Book])
	VAR _currentSub = SELECTEDVALUE('Table'[Sub])
	VAR _previousMonth = MINX(
		ALL('Table'),
		MONTH('Table'[Month])
	)
	VAR _currentMonth = MONTH(MAX('Table'[Month]))
	RETURN
		IF(
			_currentMonth <> _previousMonth,
			IF(
				HASONEVALUE('Table'[Sub]),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				)
			)
		)

        The result is as follow:

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.