Forum Discussion
Average with categories
Hi,
I have a measure which calculates the average and it shows the correct value when i display on the card visual.
However, it shows incorrect values when i place the same measure on Stack Bar Chart or any other visual like table with some dimensions and also have to show values when drilldown it on the dimensions.
These are the measures which i am using for card and Stack Bar Chart visual:
Average for Card Visual = Divide (TotalCount, TotalYears,0)
Average for Stack Bar Chart Visual = Calculate (Average for Card Visual, ALLEXCEPT('Table', 'Table'[Category1], 'Table'[Category2], 'Table'[Category3]))
I am placing measures and dimensions on the stack bar chart like this:
- Category1,Category2,Category3 are placed on Y-axis,
- Average for Stack Bar Chart Visual on X-axis
- Rating on Legend
Can you please suggest what need to done in the measures to get the correct nos. on stack bar chart and which shows correct nos. as well when drill down it from Category1 to Category2 and then to Category3.
Thanks
3 Replies
- lbendlin
Super User
Please 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/1447523Hi,
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.
