cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

ParentGroupGroupNameMaxCount
1A100
1B200
2C150
3D75

 

 

WorkData

DateGroupNameCategoryHostCount
03-18-2020AA1Host110
03-18-2020AA2Host270
03-18-2020AA1Host35
03-19-2020AA2Host120
03-20-2020BA1Host1015
03-20-2020BA2Host1120
03-18-2020CA2Host1415
03-19-2020CA1Host1425

 

please help. TIA

 

 

 

1 ACCEPTED SOLUTION

Hi,

 

Please try to create this table first:

Table = UNION(DISTINCT('WorkData'[Category]),{{"MaxCount"}})

30.PNG

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:

32.PNG

Here is my changed pbix file:

pbix 

 

Best Regards,

Giotto Zhi

View solution in original post

7 REPLIES 7
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, i choose GroupData and WorkData tables to test without calendar table.

50、.PNG

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:

51.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

Anonymous
Not applicable

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
Not applicable

Chart.JPG

Hi,

 

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

Thanks!

 

Best Regards,

Giotto Zhi

Hi,

 

Please try to create this table first:

Table = UNION(DISTINCT('WorkData'[Category]),{{"MaxCount"}})

30.PNG

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:

32.PNG

Here is my changed pbix file:

pbix 

 

Best Regards,

Giotto Zhi

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.