Filter out 0,0 results in row subtotals in matrix

[Lore_BI]

Hi guys, 

I have a question about a Matrix i made in power bi desktop. 

I have a matrix with 3 levels of rows:

- 'Fact' [ReferentieFactuurNummer]

- 'Fact'[EntiteitCode]: 

- 'Grootboek'[GrootboekrekeningNaam].

The column is 'Fact'[Icopartner'] , where there are 2 Icopartner values: "BFM" and "BTT" 

 the values of the matrix is a measure: 'Fact'[_Bedrag YTD]. 

I added  column subtotals. 

it looks like this: 

Now my question is this: I want to filter away all rows, where the subtotal at the first level, so at 'Fact' [ReferentieFactuurNummer] level, is 0. 

So no matter what the  subtotal is for the underlying row levels 'Fact'[EntiteitCode] and 'Grootboek'[GrootboekrekeningNaam], i don't want to see the lines where the subtotal of [ReferentieFactuurNummer] level, is 0,00. 

I tried lots of dax measures but i don't seem to manage to find a solution. 

Can anyone help me out? 

Thanks! 

[Lore_BI · Accepted solution]

I solved it by creating a summarized  table and than a calculated column where i check if the related field of the new table is zero or not. 

[Lore_BI]

@Greg_Deckler I used your logic and created measure: 

Filter =

  VAR __Table = SUMMARIZE('Fact', [ReferentieFactuurNummer], "__BFM", CALCULATE([_Bedrag YTD],'Fact'[Icopartner]="BFM"||'Fact'[Icopartner]="BTT",'Fact'[EntiteitCode]="BFM"||'Fact'[EntiteitCode]="BTT"))

  VAR __Sum = SUMX(__Table, [__BFM])

  VAR __Result = IF( __Sum = 0, BLANK(), [_Bedrag YTD])

RETURN

  __Result

However, the result is that lines where the value is 0 somwhere, are removed. instead, i only want the lines removed where the total is 0. 

result: 

thank you

[Lore_BI]

Thank you, but it's a matrix so the values are from measure _bedrag YTD; and the columns are from Icopartner, which can have value BFM or BTT. I tried your suggestion with measure _bedrag YTD, but there are still 0,00 subtotal values...

[Greg_Deckler]

@Lore_BI You'll have to add some kind of check in your measures that if the summarized data is 0 at the subtotal is 0 then return BLANK() instead of a number. Similar to MM3TR&R and measure totals in general. Let's say you keep your BFM measure, create a new measure BFM2 and use it instead of BFM in your matrix. Maybe something like:

BFM2 = 
  VAR __Table = SUMMARIZE('Table', [ReferentieFactuurNummer], "__BFM", [BFM])
  VAR __Sum = SUMX(__Table, [__BFM])
  VAR __Result = IF( __Sum = 0, BLANK(), [BFM])
RETURN
  __Result