Forum Discussion
Stacked bar chart help to use multiple values
I've 3 tables
Table - GroupData (GroupName, MaxCount)
WorkData(GroupName, HostName, CategoryName, Count, Date)
Calendar(Date, Year)
GroupData and WorkData are mapped 1-Many and Calendar-GroupData is 1-Many
On the stacked bar chart, i want to display the Group Max count along with counts for each category in the group and legend. I am unable to use multiple values along with legend i.e., use GroupData (MaxCount) and WorkData(Count) for every category (legend)
GroupData
| ParentGroup | GroupName | MaxCount |
| 1 | A | 100 |
| 1 | B | 200 |
| 2 | C | 150 |
| 3 | D | 75 |
WorkData
| Date | GroupName | Category | Host | Count |
| 03-18-2020 | A | A1 | Host1 | 10 |
| 03-18-2020 | A | A2 | Host2 | 70 |
| 03-18-2020 | A | A1 | Host3 | 5 |
| 03-19-2020 | A | A2 | Host1 | 20 |
| 03-20-2020 | B | A1 | Host10 | 15 |
| 03-20-2020 | B | A2 | Host11 | 20 |
| 03-18-2020 | C | A2 | Host14 | 15 |
| 03-19-2020 | C | A1 | Host14 | 25 |
please help. TIA
Hi,
Please try to create this table first:
Table = UNION(DISTINCT('WorkData'[Category]),{{"MaxCount"}})Then try this measure:
Measure = SWITCH(SELECTEDVALUE('Table'[Category]),"MaxCount",MAX('GroupData'[MaxCount]),CALCULATE(SUM(WorkData[Count]),FILTER('WorkData','WorkData'[Category] in DISTINCT('Table'[Category]))))The result shows:
Here is my changed pbix file:
Best Regards,
Giotto Zhi
7 Replies
You need a disconnected table for your legend that lists your groups and then something like "MaxCount". Then a measure that returns the correct values based upon the slicer value. See the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
Thank you for your quick reply but I do not understand how that would fit into my issue
It seems, if i can group by, pivot the WorkData table by category and merge the data by groupName to extract MaxCount, i would be able to easily plot the graph. I tested this in Excel but still cannot figure out how to acheive the same in power bi
Hi,
According to your description, i choose GroupData and WorkData tables to test without calendar table.
Then try to create a calculated column first:
Group&Category = WorkData[GroupName]&"-"&WorkData[Category]Try this measure:
Max Count = CALCULATE(SUM(GroupData[MaxCount]),FILTER(WorkData,WorkData[GroupName] in FILTERS(GroupData[GroupName])))Choose the above column,measure and [Count] as a stacked bar chart, it shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
