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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Bokazoit
Responsive Resident
Responsive Resident

Accounting balance remove zero rows

I have created this accounting Balance:

 

Bokazoit_0-1726654570937.png

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?

4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

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.

 

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

Joe_Barry
Super User
Super User

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




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

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


It changes nothing

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors