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 the different books.
Also what is the best dashboard visually to comapre the changes between 2 end of month? 
Please find below data :  

 

 

Thank you. 

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.

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • 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.