Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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.
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.
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.
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!
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |