Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to make a pie chart that shows similar information to my bar graph. The bar graphs is a stacked bar with data for multiple companies. I'd like to create two things:
1) A pie chart that shows the average of each piece from the bar chart
2) A pie chart of data for one specific company from the bar chart
Attached is a picture of my bar graph. The equation I'm using for the first piece on the graph (the blue piece) is this:
=(Avg({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(
Sum({1<QPermID={"Stack_Blue*"}>}Response)/
Sum({1<QPermID={"Stack*"}>}Response)
,CompanyID,Year)))
I have multiple years of data, so this uses only the max year and sorts by year and by companyID (the two digit numbers at the bottom of the bar chart).
In the pie I've created, it's not restricted to max year. I cannot figure out how to do this! Here's my current measure in my pie:
=Sum({1<QPermID={"Stack_Blue*","Stack_Gold*","Stack_Red*","Stack_Teal*"}>}Response)
Additionally, I have no idea how to add in a qualification that will allow me to filter my pie chart by companyID, if I apply that selection to my app.
Any suggestions would be appreciated! THANK YOU!
You should be able to add in the same year set analysis to your second expression:
=Sum({1<QPermID={"Stack_Blue*","Stack_Gold*","Stack_Red*","Stack_Teal*"}, Year = {$(=Max(num#(Year ,'####')))}>}Response)
Then, to that, you should be able to add the "possible" companies based on selections (your 1 at the beginning of the set analysis is ignoring all selections and why the Company selection isn't currently filtering your chart):
=Sum({1<QPermID={"Stack_Blue*","Stack_Gold*","Stack_Red*","Stack_Teal*"}, Year = {$(=Max(num#(Year ,'####')))}, Company=P(Company)>}Response)
You should be able to add in the same year set analysis to your second expression:
=Sum({1<QPermID={"Stack_Blue*","Stack_Gold*","Stack_Red*","Stack_Teal*"}, Year = {$(=Max(num#(Year ,'####')))}>}Response)
Then, to that, you should be able to add the "possible" companies based on selections (your 1 at the beginning of the set analysis is ignoring all selections and why the Company selection isn't currently filtering your chart):
=Sum({1<QPermID={"Stack_Blue*","Stack_Gold*","Stack_Red*","Stack_Teal*"}, Year = {$(=Max(num#(Year ,'####')))}, Company=P(Company)>}Response)