How to show the sum of two possible combinations of values from two columns in a Matrix?

[An0n]

Dear Power BI Community,

I have scanned the forum but could not get any solution to work for the following problem statement:

I have one table with multiple columns loaded into Power BI Desktop through Direct Query with a DB2 connection. I am trying to create a matrix which shows a combination of Column 1 and Column 2 in the rows, e.g. AAA_BBB. It should sum up all the rows for the combination AAA_BBB. This is easily done by just dragging Column 3 (Amounts) to Values in the Matrix with a merged column (Column 1 and 2) in the rows. Next I would like to show the reverse combination, e.g. BBB_AAA in the Values area. The goal is to highlight the differences between the total of matching combinations, e.g. AAA_BBB is 10 but BBB_AAA is 8. It should be equal. Would anyone have any suggestions for the measure (or calculated column?) formula to move to the desired implementation?

Data example:

Column 1	Column 2	Column 3
AAA	CCC	10
AAA	BBB	5
CCC	BBB	4
BBB	CCC	3
CCC	AAA	10
BBB	AAA	8
AAA	BBB	2
AAA	BBB	3
BBB	CCC	1

Desired implementation:

Column 1 x 2	Amount Combination 1	Amount Combination 2	Difference
AAA_BBB	10	8	2
AAA_CCC	10	10	0
BBB_CCC	4	4	0

Thanks in advance for your suggestions.

[v-yangliu-msft]

Hi  @An0n ,

If you want to sort by [Difference], you can do so directly.

Select the ellipsis, select Sort by-[Difference], and sort descending.

Best Regards,

Liu Yang

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

[An0n]

Thanks for this suggestion, which would indeed solve the sorting problem.

Could you have a look at how to adapt the "IN AAA_BBB, AAA_CCC, BBB_CCC" part below? In the dataset there are a lot more (around 50) values of AAA, BBB etc. and the amount of different combinations and new values changes from one period to the other.

Amount Combination 1 =
CALCULATE(
    SUM('Table'[Column 3]),
    FILTER(ALL('Table'),'Table'[Column 1 x 2]=MAX('Table'[Column 1 x 2])&&'Table'[Column 1 x 2]IN {"AAA_BBB","AAA_CCC","BBB_CCC"}))

[v-yangliu-msft]

Hi  @An0n ,

Here are the steps you can follow：

1. Create calculated column.

Column 1 x 2 = 'Table'[Column 1]&"_"&'Table'[Column 2]

2. Create measure.

Amount Combination 1 =
CALCULATE(
    SUM('Table'[Column 3]),
    FILTER(ALL('Table'),'Table'[Column 1 x 2]=MAX('Table'[Column 1 x 2])&&'Table'[Column 1 x 2]IN {"AAA_BBB","AAA_CCC","BBB_CCC"}))

Amount Combination 2 =
VAR _PIN=MAX('Table'[Column 2])&"_"&MAX('Table'[Column 1])
return
IF([Amount Combination 1]=0,BLANK(),
CALCULATE(SUM('Table'[Column 3]),FILTER(ALL('Table'),[Column 1 x 2]=_PIN)))

Difference = [Amount Combination 1]-[Amount Combination 2]

3. Result:

Best Regards,

Liu Yang

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

[An0n]

Hi Liu Yang,

Thank you for your reply. The AAA BBB and CCC was just as an example, in reality there are around 60 entries and thus a multiple of that as possibilities (not every combination exists, e.g. XXX and TTT). There is also no requirement to order column 1 x 2 in any way, the important thing is that the difference can be sorted from high to low. Hope this helps and look forward to your suggestion.

[Jihwan_Kim]

https://www.dropbox.com/s/1p7uhxi1ulzsrvo/anon.pbix?dl=0 

[An0n]

Thank you for the suggestion. The combination of AAA and BBB should however be shown in one column and as one row entry only (AAA_BBB should be shown, without BBB_AAA). There are hundreds of combinations in the original dataset so it should be easy to see just see the difference of the sum for this possible combination.

[Jihwan_Kim]

https://www.dropbox.com/s/1p7uhxi1ulzsrvo/anon.pbix?dl=0 

[An0n]

Thank you Jihwan_Kim, the solution is taking shape. Unfortunately I ran into the following bug: the calculated column Rank CC does not work because function 'RANKX' is not allowed as part of calculated column DAX expressions on DirectQuery models and I am using a DirectQuery model.

[Jihwan_Kim]

https://www.dropbox.com/s/1p7uhxi1ulzsrvo/anon.pbix?dl=0 

Ranking =

RANKX ( ALL(Data[AB Axis CC]), CALCULATE(MAX(Data[AB Axis CC])),, ASC )

AB combination total: =
VAR _currentrank = [Ranking]
VAR _currentaxis =
MAX ( Data[AB Axis CC] )
VAR _currentA =
MAX ( Data[A] )
VAR _currentB =
MAX ( Data[B] )
VAR _newtable =
ADDCOLUMNS (
ALL ( Data ),
"@Rank", RANKX ( ALL ( Data[AB Axis CC] ), Data[AB Axis CC],, ASC )
)
RETURN
IF (
COUNTROWS (
FILTER (
_newtable,
Data[AB Axis CC] <> _currentaxis
&& Data[A] = _currentB
&& Data[B] = _currentA
&& [@Rank] < _currentrank
)
) >= 1,
BLANK (),
IF ( ISFILTERED ( Data[AB Axis CC] ), SUM ( Data[Value] ) )
)

[An0n]

I appreciate the effort a lot. It seems I'm running into a final bug: "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

I was also looking to show this in Matrix format, so that I'll be able to scale the visualisization later by having other columns of the dataset as rows.

As I understand it currently, the ranking is not required. The AB Axis CC column can have the combinations ordered in any way, it doesn't have to be alphabetically. The only requirement is to be able to sort the Diff column from high to low.