cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dwel0120
Helper II
Helper II

Adding an ALL to the legend bar graph

I am creating a bar chart that shows the percentages of each response category. I want to look at Fall and Spring separately but I also want to add an ALL along with the Fall and Spring. My screenshot is the first image and my goal is the second image. 

 

dwel0120_0-1634742454691.png

 

dwel0120_1-1634742555499.png

 

1 ACCEPTED SOLUTION

@dwel0120 I hope attached is what you are looking for?

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

13 REPLIES 13
parry2k
Super User
Super User

@dwel0120 it is not letting me download the file, asking for request access. Can you share it to public?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@dwel0120 I hope attached is what you are looking for?

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

@parry2k One more question on this.

 

Sometimes I don't have a spring survey, so I don't need the "all" data to show up. How would I make it to where if I dont have spring then don't show all because it is the exact same data.

 

dwel0120_0-1635445655985.png

 

This works perfectly! Thank you so much for all of your help. 

parry2k
Super User
Super User

@dwel0120 not sure why. can you share pbix file with sample data, remove sensitive information before sharing, you can share using one drive/google drive. Cannot tell anything without looking at the data.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@dwel0120 did you check my sample file, use that TREATAS function in your % measure:

 

%Response =
CALCULATE (
DIVIDE (
COUNTA('Table1'[Response]),
CALCULATE ( COUNTA('Table1'[Response]), ALL('Table1'[Response]) )
), 

<<here goes the treatas logic>>
)

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k   I received an error with the below formula
 
Response% = CALCULATE (
DIVIDE (
COUNTA([Response]),
CALCULATE ( COUNTA([Response]), ALL('Table1'[Response] )
),
TREATAS ( VALUES ( Season[SurveySeason] ), 'Table1'[SurveySeason] )
)
)
 
 

Feedback Type:

Error Message:
MdxScript(Model) (14, 1) Calculation error in measure 'Table1'[Response%]: The alternate result to return on divide by zero cases must be a constant numeric value.

 

AlexisOlson
Super User
Super User

Check out these similar posts:

https://community.powerbi.com/t5/Desktop/Grand-Total-in-Bar-Chart/m-p/612631

https://community.powerbi.com/t5/Desktop/How-to-get-a-quot-Total-column-quot-in-the-clustered-column...

 

Your question is similar but would use the column containing "All" in the legend field rather than the x-axis.

parry2k
Super User
Super User

@dwel0120 see attached for reference, tweak it as you see fit.

 

parry2k_0-1634743354568.png

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@dwel0120 you need to create a dimension table for Survey Season with 3 rows and it will be an independent table, it will have 3 rows Fall, Spring and All, and then in DAX measure use this new table to get the logic working.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k My percentages are off now. What do you mean by in the dax measure use the new table. Here is my dax measure to get percetages. 

 

%Response =
DIVIDE (
COUNTA('Table1'[Response]),
CALCULATE ( COUNTA('Table1'[Response]), ALL('Table1'[Response]) )
)

 

dwel0120_0-1634746491821.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.