Stacked bar chart help to use multiple values

[Anonymous]

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

[v-gizhi-msft]

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:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thank you for you help but requirement has changed and this isn't needed any more. The output i was looking for is as below. Also, legend needs to show the category names

[Anonymous]

[v-gizhi-msft]

Hi,

If my answer has solved your issue, please mark it as a solution for others to see.

Thanks!

Best Regards,

Giotto Zhi

[v-gizhi-msft · Accepted solution]

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:

pbix 

Best Regards,

Giotto Zhi

[Greg_Deckler]

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...

[Anonymous]

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