Difference of two columns for categories

[Pikachu-Power]

Hi all,

I count the entries in two columns in the same table:

CountX = CALCULATE(COUNT(Tabel1[ColumnX]), FILTER(Tabel1, Tabel1[ColumnX] <> Tabel1[ColumnY]))

CountY = CALCULATE(COUNT(Tabel1[ColumnY]), FILTER(Tabel1, Tabel1[ColumnX] <> Tabel1[ColumnY]))

The result I get is:

What I want is the difference of equal categories. For example A from column X minus A from ColumnY = 11. See also picture above.

Some ideas? 

Many thanks.

[VahidDM · Accepted solution]

Hi @Pikachu-Power 

Try this measure:

Difference = 
Var _Item = FIRSTNONBLANK('Table'[ColumnX],"")
Var _ValueX = sumx(filter('Table','Table'[ColumnX]=_Item),'Table'[ValueX])
Var _ValueY = sumx(filter(all('Table'),'Table'[ColumnY]=_Item),'Table'[ValueY])
return
_ValueX-_ValueY

The output will be as follow:

Table I used in Power BI:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[Pikachu-Power]

Both cases dont work yet. I suppose because i also have filtered Tabel1[Year] = 2019 that get ignored through the ALL command?

@Vahid With Table[ValueX] you mean Table[CountX]? My measure before?

[Pikachu-Power]

@VahidDM

I modivied ALL(Table) to ALLEXCEPT(Table, Table(Year)) and it seems to work 🙂

[ryan_mayu]

@Pikachu-Power 

maybe try

DIFF = COUNT(Tabel1[ColumnX])- CALCULATE(COUNT(Tabel1[ColumnY]), FILTER(ALL(Tabel1), Tabel1[ColumnY] =MAX(Tabel1[ColumnX]))