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

[RenatoM]

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:

Expected result:

I've attached the sample PBIX file

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

[RenatoM]

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.

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.

[RenatoM · 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.

[Uzi2019]

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 

take matrix visual and hide the row subtotal

or you can take table visual.

I hope I answered your question!

[RenatoM]

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. 

[v-tianyich-msft]

Hi @RenatoM ,

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

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.