Forum Discussion
Calculating the Difference between columns in matrix visual
I've been stumped with the solution to this problem. I have a matrix table that produces this output.
The desired output is something like this ( figures are made up)
I want to display the difference in ranking and CT% (Market Share) between the value in a row and the following value in the next column within the row. There are no dates within the source data the output is a string of the respective period.
Within the data, you have Years and Quarters within the period field represented below:
example for 2021: 21Q1, 21Q2, 21Q3, 21Q4, 21Y
I wish not to be restricted to QoQ and YoY, for example, if a user picks Q4 2022 and Q1 2020 I would like to be able to show the difference between the selected values.
Many thanks,
Hi GotData
Without knowing how your data looks like, I assume that your data is like below in the table.
You need to have a period sort table in the model with an Index column in chronological order. Connect this table to the source table on Period column. Then use this table's Period column in the matrix visual and the slicer visual.
Then you can create measures like below
Difference in U = var _curPeriodIndex = MAX('Period Sort'[Index]) var _prePeriodIndex = CALCULATE(MAX('Period Sort'[Index]),ALLSELECTED('Period Sort'),'Period Sort'[Index] < _curPeriodIndex) return IF(ISBLANK(_prePeriodIndex),BLANK(),SUM('Table'[Units]) - CALCULATE(SUM('Table'[Units]),ALLEXCEPT('Table','Table'[Brand]),'Period Sort'[Index] = _prePeriodIndex))Rank Difference = var _curPeriodIndex = MAX('Period Sort'[Index]) var _prePeriodIndex = CALCULATE(MAX('Period Sort'[Index]),ALLSELECTED('Period Sort'),'Period Sort'[Index] < _curPeriodIndex) return IF(ISBLANK(_prePeriodIndex),BLANK(),MAX('Table'[Rank]) - CALCULATE(MAX('Table'[Rank]),ALLEXCEPT('Table','Table'[Brand]),'Period Sort'[Index] = _prePeriodIndex))%CT Difference = var _curPeriodIndex = MAX('Period Sort'[Index]) var _prePeriodIndex = CALCULATE(MAX('Period Sort'[Index]),ALLSELECTED('Period Sort'),'Period Sort'[Index] < _curPeriodIndex) return IF(ISBLANK(_prePeriodIndex),BLANK(),SUM('Table'[%CT]) - CALCULATE(SUM('Table'[%CT]),ALLEXCEPT('Table','Table'[Brand]),'Period Sort'[Index] = _prePeriodIndex))The result:
You can use the slicer to select incontinuous periods. It will show the difference between selected periods correctly.
The sample pbix file has been attached at bottom. Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
1 Reply
Hi GotData
Without knowing how your data looks like, I assume that your data is like below in the table.
You need to have a period sort table in the model with an Index column in chronological order. Connect this table to the source table on Period column. Then use this table's Period column in the matrix visual and the slicer visual.
Then you can create measures like below
Difference in U = var _curPeriodIndex = MAX('Period Sort'[Index]) var _prePeriodIndex = CALCULATE(MAX('Period Sort'[Index]),ALLSELECTED('Period Sort'),'Period Sort'[Index] < _curPeriodIndex) return IF(ISBLANK(_prePeriodIndex),BLANK(),SUM('Table'[Units]) - CALCULATE(SUM('Table'[Units]),ALLEXCEPT('Table','Table'[Brand]),'Period Sort'[Index] = _prePeriodIndex))Rank Difference = var _curPeriodIndex = MAX('Period Sort'[Index]) var _prePeriodIndex = CALCULATE(MAX('Period Sort'[Index]),ALLSELECTED('Period Sort'),'Period Sort'[Index] < _curPeriodIndex) return IF(ISBLANK(_prePeriodIndex),BLANK(),MAX('Table'[Rank]) - CALCULATE(MAX('Table'[Rank]),ALLEXCEPT('Table','Table'[Brand]),'Period Sort'[Index] = _prePeriodIndex))%CT Difference = var _curPeriodIndex = MAX('Period Sort'[Index]) var _prePeriodIndex = CALCULATE(MAX('Period Sort'[Index]),ALLSELECTED('Period Sort'),'Period Sort'[Index] < _curPeriodIndex) return IF(ISBLANK(_prePeriodIndex),BLANK(),SUM('Table'[%CT]) - CALCULATE(SUM('Table'[%CT]),ALLEXCEPT('Table','Table'[Brand]),'Period Sort'[Index] = _prePeriodIndex))The result:
You can use the slicer to select incontinuous periods. It will show the difference between selected periods correctly.
The sample pbix file has been attached at bottom. Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.