Forum Discussion
Bar chart levels percentage
Hello people,
I have a stacked bar chart with 2 levels (see table)
Each bars has a total of queries and a query percentage of it
Eg.
If total queries are 1000:
* In the 1st level:
Bar A must show 100 (# of queries in category 0 level) and 0.10 % (A / 1000), where A = A1+A2+A3+A4+A5 = 100
Bar B must show 0 (# of queries in category 0 level) and 0 % (B / 1000), where B = B1 = 0
and so on.
* IF i do click in the Bar A, In the 2nd level it must shows:
Bar A1 must show 10(# of queries in category 1 level) and 0.10 % (A1 / A)=(10/100), where A = A1+A2+A3+A4+A5 = 100
Bar A2 must show 20(# of queries in category 1 level) and 0.20 % (A2 / A)=(20/100), where A = A1+A2+A3+A4+A5 = 100
and so on.
The bar chart shows # of queries and percentage ok at the category 0 level.
But, When i do a drill down in A bar, the bars percentage in the 2nd level (category 1) are wrong.
| request_number_id | category0 | category1 | Queries |
| 1 | A | A1 | 10 |
| 2 | A | A2 | 20 |
| 3 | A | A3 | 30 |
| 4 | A | A4 | 40 |
| 5 | A | A5 | 0 |
| 6 | B | B1 | 0 |
| 7 | B | C1 | 200 |
| 8 | D | D1 | 300 |
| 9 | E | E1 | 400 |
So far i'm using this measure to calculate the percentage of each bar:
Query By Cat_%_0 =
VAR A = DIVIDE( DISTINCTCOUNT(VW[request_number_id]), [_TOT_Cat_0]) * 100
RETURN "( " & FORMAT( A, "#,##0.0") & " % )"
_TOT_Cat_0 = CALCULATE( DISTINCTCOUNT(VW[request_number_id]), LEFT(VW[category0],1) <> "(")
Anybody have a different approach or an improvement about this ?. Thank you!
Thankyou, Ritaf1983, burakkaragoz, for your response.
Hi carrascojg,We appreciate your question on the Microsoft Fabric Community Forum.
Please find the attached screenshots and PBIX file, which may help solve the issue:
If our response is helpful, please mark it as the accepted solution and give kudos. This will help other community members with similar questions.
If you have any further questions, feel free to ask the Microsoft Fabric community.
Thank you.
6 Replies
- Ritaf1983
Super User
Hi carrascojg
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - burakkaragoz
Hi carrascojg ,
Yeah, this is a common issue when working with drill-downs in stacked bar charts. The key is that your denominator needs to be dynamic based on the current level of hierarchy.
Try using ISINSCOPE() to detect the current level and adjust the calculation accordingly. Here's a pattern that usually works better:
Query By Cat_%_0 = VAR TotalAtLevel = IF( ISINSCOPE(W[category1]), CALCULATE(DISTINCTCOUNT(W[request_number_id]), ALLEXCEPT(W, W[category0])), CALCULATE(DISTINCTCOUNT(W[request_number_id])) ) VAR CurrentCount = DISTINCTCOUNT(W[request_number_id]) VAR Result = DIVIDE(CurrentCount, TotalAtLevel) RETURN FORMAT(Result, "0.0%")This way, when you're at level 1 (category1), it uses the total from category0 as the base. Otherwise, it uses the grand total.
Also check out this Microsoft doc on ISINSCOPE() and drill-down behavior:\ 👉 https://learn.microsoft.com/power-bi/create-reports/desktop-drillthrough
Let me know if this helps or if you want to tweak it further.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
v-pnaroju-msftCommunity Support
Thankyou, Ritaf1983, burakkaragoz, for your response.
Hi carrascojg,We appreciate your question on the Microsoft Fabric Community Forum.
Please find the attached screenshots and PBIX file, which may help solve the issue:
If our response is helpful, please mark it as the accepted solution and give kudos. This will help other community members with similar questions.
If you have any further questions, feel free to ask the Microsoft Fabric community.
Thank you.
- v-pnaroju-msft
Hi carrascojg,
We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.
Thank you. - v-pnaroju-msft
Hi carrascojg,
We are checking in to know if the information we provided helped in resolving your issue.
If you have any further questions, please do not hesitate to contact the Microsoft Fabric community.
Thank you. - v-pnaroju-msft
Hi carrascojg,
We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.
If our response was helpful, please mark it as the accepted solution, as this helps the broader community.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.
