Forum Discussion
Helper IIDifference between two columns that only contain text
I am attempting to create a comparison between two columns in a matrix, but I am hitting a wall.
Here is some sample data:
| Source | Leaving | Day of Week | Arriving | Company |
A1 | Phoenix | Monday | Los Angeles | A |
| A2 | Phoenix | Thursday | Las Vegas | B |
| A1 | Phoenix | Friday | San Diego | B |
| A9 | Phoenix | Monday | Los Angeles | A |
(But imagine thousands of rows)
How I currently have the matrix set up is:
Rows = Arriving
Columns = Source
Values = Count of Leaving
There are filters on the page for Everything except Arriving.
I am currently displaying columns that show a total number of the filtered rows, allowing users to select more than one of any filter to get the data they need. But I want to show the difference between two sources.
| Arriving | A1 | A2 | Difference |
| Los Angeles | 50 | 45 | 5 |
| San Diego | 12 | 15 | -3 |
| Las Vegas | 500 | 390 | 110 |
How do I calculate the difference between the two columns if what I am counting is only text? I just want to count how many times any combination occurs and show the difference between the two selected and using whatever I put in column 1 as a baseline for the subtraction.
2 Replies
syasmin25Helper V
Hello, if I have understood your question properly, then here is a possible solution. Let me know if it helps.
This is the sample table that I had taken from your question.
As for the calculation, I created 3 and inserted them into the regular table instead of the matrix table.
I created a measures for A1 & A2A1 = CALCULATE(COUNT('Table'[Source]),FILTER('Table','Table'[Source] = "A1"))A2 = CALCULATE(COUNT('Table'[Source]),FILTER('Table','Table'[Source] = "A2"))
Afterwards, I created calculation for the difference a follows,Differnce = [A1]-[A2]And then I inserted the following into the table and it looks the the visual below,
DreDreThe downside is that I would like to avoid a hard reference in the calc because the list of sources grows monthly, and I already have over 100. With the number of Arrival cities that there are a matrix made more sense so that we could see the totals side by side rather than stacked. If they were stacked you would have to keep scrolling up and down to find the difference or add another slicer and go city by city still. I had found this other thread that was doing something simillar, but I was not able to figue out the count with a column of text, I was thinking count, or counta, but neither worked (probably user error).
Link: Other Forum Thread