Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DreDre
Helper II
Helper II

Difference 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:

SourceLeavingDay of WeekArrivingCompany

A1

PhoenixMondayLos AngelesA
A2PhoenixThursdayLas VegasB
A1PhoenixFridaySan DiegoB
A9PhoenixMondayLos AngelesA

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

ArrivingA1A2Difference
Los Angeles50455
San Diego1215-3
Las Vegas500390110


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 2
syasmin25
Helper V
Helper 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.

syasmin25_0-1597246030570.png

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 & A2

A1 = 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, 
 
 

tbruh.PNG

The 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.