Forum Discussion
Average with categories
Super UserPlease provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi,
The below is the sample data where first I have to calculate Average (DistinctCount(PST)/DistinctCount(Year)) then i have to show this Average value on a Stack bar chart by placing the measures and fields like this, also this Average should
show correct values when doing drill down from Main Category to Category to Sub Category:
Y-axis: Main Category
Category
Sub Category
X-axis: Average
Legend: Ranking
| Main Category | Category | Sub Category | PST | Ranking | Year |
| ACC | AP | Accounting | 2256_763_4567_OP2256_763_4567_OP | Medium | 2024 |
| ACC | AP | Accounting | 2256_763_4567_OP2256_763_4567_OP | Medium | 2024 |
| ACC | AP | IT | 2256_763_4567_OP2256_763_4567_OP | Medium | 2024 |
| ACC | TS | Accounting | 2256_763_4567_OP2256_763_4567_OP | Oral | 2024 |
| ACC | TS | Disbursements | 2256_763_4567_OP2256_763_4567_OP | Low | 2024 |
| ACC | TS | Disbursements | 2256_763_4567_OP2256_763_4567_OP | Low | 2024 |
| BD | BDA | Commissions | 2256_763_4567_OP2256_763_4567_OP | Oral | 2024 |
| BD | BDA | Commissions | 2256_763_4567_OP2256_763_4567_OP | Oral | 2023 |
| BD | BDA | Commissions | 2256_763_4567_OP2256_763_4567_OP | Low | 2023 |
| BD | FDE | Commissions | 2256_763_4567_OP2256_763_4567_OP | Oral | 2024 |
| BD | FDE | Security | 20042256_763_4567_OP5712256_763 | Best | 2024 |
| BD | FDE | Security | 4567_OP39242256_763_4567_OPOP | Best | 2024 |
| BAAL | MSB | Currency Exchange Recordkeeping | 23322256_763_4567_OP6832256_763 | Best | 2021 |
| BAAL | MSB | Currency Exchange Recordkeeping | 4567_OP39272256_763_4567_OPOP | Best | 2021 |
| BAAL | MSB | FinCEN 314(b) Requirements | 2256_763_4567_OP2256_763_4567_OP | Medium | 2021 |
| BAAL | MSB | FinCEN 314(b) Requirements | 2256_763_4567_OP2256_763_4567_OP | Medium | 2025 |
| BAAL | MSB | FinCEN 314(b) Requirements | 2256_763_4567_OP2256_763_4567_OP | Oral | 2022 |
| BAAL | MSB | FinCEN 314(b) Requirements | 2256_763_4567_OP2256_763_4567_OP | Best | 2022 |
| BAAL | MSB | FinCEN 314(b) Requirements | 20082256_763_4567_OP5832256_763 | Low | 2021 |
| BAAL | MSB | FinCEN 314(b) Requirements | 20082256_763_4567_OP5832256_763 | Low | 2021 |
- lbendlin
Like this?
And no, Power BI does not support dynamic bucketing. You need to bring your own, predefined buckets.