Forum Discussion

Bokazoit's avatar
Bokazoit
Continued Contributor
1 year ago

Accounting balance remove zero rows

I have created this accounting Balance:

 

Each year is calculated as all transactions for a given account-number since beginning of time 🙂

To do that I use this measure:

 

[Bogført beløb (Balancen)] = 

VAR LastYear = MAX( DimDato[Dato] )

CALCULATE ( 
   SUM ( FactFinans[#Bogførtbeløb] ),                                            
   FILTER ( ALL ( Dimdato ), DimDato[Dato] <= LastYear )   ,                    
   DimKonto[Kontonummer] >= 6000                                     
   )

 

As can be seen from the above screendump, there is several accounts were the measure return 0 (zero). So for that reason they would like to be able to choose between showing all accounts with or without 0 values. To that I could use this measure:

 

Remove row = 

VAR NulPost = IF (CALCULATE (ROUND([Bogført beløb (Balancen)],2), ALLSELECTED(DimKonto[Konto])) = 0.00, 0, 1)
VAR Valg = CALCULATE( MIN(Nulposter[Svar værdi]))

RETURN

IF (NulPost >= Valg, 1, BLANK())

 

but it removes account 5028 and 5029 because it calculate for the last year (2022) were the value calculated is zero (0).

 

I have thought of a method were I can say:

 

1. Calculate per account per year

2. If max value per account is not zero then keep line else remove

 

But how to do that?

Need Help

4 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    1 year ago

    Hi Bokazoit 

     

    Depending on the timeframe you have chosen, there could be entries in prevous years that are in your slicers timeframe. Leave the original measure in the visual, highlight the visual and in the filter pane on the left, go to the measure and choose "Is not 0".

    If that doesn't work adapt your measure

    [Bogført beløb (Balancen)] = 

VAR LastYear = MAX( DimDato[Dato] )
IF (
CALCULATE ( 
   SUM ( FactFinans[#Bogførtbeløb] ),                                            
   FILTER ( ALL ( Dimdato ), DimDato[Dato] <= LastYear )   ,                    
   DimKonto[Kontonummer] >= 6000                                     
   ) = 0, BLANK(), 
CALCULATE ( 
   SUM ( FactFinans[#Bogførtbeløb] ),                                            
   FILTER ( ALL ( Dimdato ), DimDato[Dato] <= LastYear )   ,                    
   DimKonto[Kontonummer] >= 6000                                     
   )


    Joe

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solution Joe_Barry offered and i want to offer some more information for user to refer to.

    hello Bokazoit ,  if you want to hide the value that returns 0, it is better that you use the table visual instead of matrix, because in matrix visual, the values of 2021 and 2022  are mutually influential, as long as one of them is 0, the entire data will be hidden. you can refer to the following similar thread.

    Solved: Hide matrix row with value 0 - Microsoft Fabric Community

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Bokazoit's avatar
      Bokazoit
      Continued Contributor
      1 year ago

      I think You need to read my post again. I am aware of the functionality of using the measures and I know that it should not remove those with data previous years. That is not what I am aksing about. I seek a solution were the rows with zeros in the above picture account 5030 is removed as an example but not 5028 or 5029