cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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

10 REPLIES 10
Community Support

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

Frequent Visitor

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

Community Support

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

Frequent Visitor

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.

Super User

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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.

Super User

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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.

Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors