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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RenatoM
Frequent Visitor

Matrix: filter out all rows where the subtotal level is 0,00

I have a matrix with a hierarchy like below which can be drilled down to a "Note" level.

I need to analyze the rows where the subtotal is not 0,00, so I want to have the possibility to display the rows where the subtotal level is not equal to 0,00. At the same time, i would like to display (when needed) all the information (a complete table with all rows and subtotals)

So no matter which level the subtotal is 0,00 from that point beyond (including the row where the subtotal is 0,00), I don't want to see the lines, or when necessary display all the table (all rows).

Please see below example. Great if anyone can help.

 

Actual Table:

RenatoM_2-1726031141621.png

 

 


Expected result:

RenatoM_1-1726030835536.png

 

I've attached the sample PBIX file

 

https://drive.google.com/drive/folders/1ob7XVSNsTkdGt2Ijz2VgUhNsDc0kSh9i?usp=sharing

 

 

1 ACCEPTED SOLUTION

Hi Community,

I found the solution. I figure out that some amounts are smallter than .01, so I changed my Mesure by adding: 

ROUND(<number>, <num_digits>)

In my case <num_digits> = 2

After this change the visualization worked.

 

View solution in original post

5 REPLIES 5
RenatoM
Frequent Visitor

Hi @v-tianyich-msft thanks for the idea! I tried to add more register into the table by applying your model but  it didn't work out with my complete database.

 

I did a research and found a similar situation that was resolved : (https://community.fabric.microsoft.com/t5/Desktop/Remove-0-s-in-a-row-sum/td-p/2178291)

 

I tried to replicate it to my cenario case but still remain the same problem.
I've attached a new sample below and I dont understand why some records are still visible while even when add a Filter.

 

RenatoM_0-1726231704332.png

 

 

https://drive.google.com/drive/folders/1NMnaiHWw6nc3un4FLd3SKfHnHYXs7M2j?usp=drive_link

 

Thank's in advance for reading my problem. I would appreciate any help on this request.

 

Hi Community,

I found the solution. I figure out that some amounts are smallter than .01, so I changed my Mesure by adding: 

ROUND(<number>, <num_digits>)

In my case <num_digits> = 2

After this change the visualization worked.

 

Uzi2019
Super User
Super User

Hi @RenatoM 

As per your data you just have 5 records . but you have taken the data in hierarchy form that why it is showing 0 where it higher level is not found..

 

please check the alternate approach to represent your data..

 

Actual data 

 

Uzi2019_0-1726036201222.png

 

take matrix visual and hide the row subtotal

Uzi2019_1-1726036265443.png

 

 

or you can take table visual.

Uzi2019_2-1726036298183.png

 

 

I hope I answered your question!

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Hi @Uzi2019  Thank you for the response.

That's right this sample has only 5 records however my database has a volume of about 100 thousand records in our fact table, with a monthly transactions of about 10 thousands rows of data.

By the end of the day I need do check those records that are not summed to zero. Basically if everything went well all records will have summed to zero starting from lower level "Document" subtotal.

The alternate approach you´ve mentioned to represent my data would keep displaying all records.

 

It would be helpful if I could filter out all rows with sub-total = 0,00, in other words I would like to see only sub-row for subtotal more then 0, the rest subrows (including it's subtotal) should not be visible as they have a subtototal = 0. 

 

 

Hi @RenatoM ,

 

Because of the policy, I can't download your PBIX file, I made simple samples and you can check the results below:

vtianyichmsft_0-1726128170505.png

Measure = var _sum = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Company Code]))
RETURN IF(_sum=0,BLANK(),_sum)

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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