Forum Discussion
Use Measure as Axis in visual
I solved the issue. I created a new table with the axis lables in the first column, then used If statements to look at the value in the first column. If it was one of the subtotals, it returned the measure I created. If it was an actual status, I used:
CALCULATE(counta('Idea Factory One Utility'[Status Of Idea]),
FILTER(all('Idea Factory One Utility'), 'Idea Factory One Utility'[Status Of Idea]=Idea_Status[Status Type]))
Hi, I assume that you have a whole table of data that has all the projects on it with some sort of mixture of flags that in an order give you whatever the status is. What you will want to do is make a calculated column (on the modelling tab) and make a column that has all the logic in it to create each of teh the statuses you want on your axis. This is just an IF() statement or if you want a SWTICH(). SO you will have something like:
Status = IF(OR(Table[status] = "accepted",Table[status] = "just about accepted"),"Accepted" ...etc.... )))
Satus = SWITCH(Table[status], "accepted","Accepted","just about accepted","Accepted" ....etc... )))
You then use the status column as the x axis. You should need any other measures that a count of projects..
// If this is an answer to your question please mark as such
That gets me part of the way there. I need to also include a "subtotal" as a vlaue on the axis. Here is the original graph that I'm trying to recreate
.
The grey bars are sub totals of the actual statuses. It was created in excel using three series. The Grey bar is one, the orange is another, and the "clear" bar is the third. In the series for the grey bars, the value for the orange and clear is zero, making it appear they are not there. This can be acomplished because I can manually indicate the values for the axis and then assign data to the three series.
I've got all the values I need, I created them using measures, but I can't figure out how to get that measure to align with an axis label. Is there a way to create a new table with the axis labels in a column, and then refernce the measures in the next column?
Any help is appreciated.
I solved the issue. I created a new table with the axis lables in the first column, then used If statements to look at the value in the first column. If it was one of the subtotals, it returned the measure I created. If it was an actual status, I used:
CALCULATE(counta('Idea Factory One Utility'[Status Of Idea]),
FILTER(all('Idea Factory One Utility'), 'Idea Factory One Utility'[Status Of Idea]=Idea_Status[Status Type]))I solved the issue. I created a new table with the axis lables in the first column, then used If statements to look at the value in the first column. If it was one of the subtotals, it returned the measure I created. If it was an actual status, I used:
CALCULATE(counta('Idea Factory One Utility'[Status Of Idea]),
FILTER(all('Idea Factory One Utility'),
'Idea Factory One Utility'[Status Of Idea]=Idea_Status[Status Type]))This retuned the values that I needed for the visualtion. Its a little brute force, but it worked.
Hi taylorrc, it seems a great solution !
But I don't understand how you inplement your "IF statements" to look at your table with x labels
and return the correct measure : subtotal or the value.
Could show me the differents steps you implemented ?
Thank you and have a nice day !
Fred
