I'm trying to create a graph with a time axis that is NOT date, but "rounds" (represented by integers), and would like to do a cumulative graph equivalent to YTD (but based on this integer). In addition to this, I would like to select which round is shown, and show the cumulative value for all the rounds up to and including in a line graph. So, for instance, if I have for the rounds 1, 2 and 3 the values of 3, -5 and 10, the graph should plot the values of 3, -2 (3-5) and 8 (3-5+10) for the rounds, but if I select round 2, only the first two will be shown, etc. In addition to this, I want the graph to have the same scale, at least for the X axis, meaning that I would like all rounds to exist on the axis, even if the data is not plotted (preferrably, the Y axis should be the same).
So, for the solution, I have figured out I can use a separate table that only has the rounds (no relation), and then have a filter in a measure. To make the X axis fix, I use a series that has all 0s in it, and filter on either values for the series up to and including the round selected by the filter OR the special series. That produces a line around 0 (which I can live with when I change the color to something neutral).
I am struggling a bit with the cumulative part, however. I currently have a measure that looks a bit like this:
MyMeasurement = CALCULATE( SUM( MyTable[ValueColumn] ) ; FILTER( MyTable ; MyTable[Series] = "-" || MyTable[Round] <= SUM( RoundTable[Round] ) ) )
I am trying to somehow remove the filters, but it seems if I replace MyTable in the FILTER with ALL, ALLEXCEPT, or ALLSELECTED, it either doesn't filter correctly, or it doesn't work because it returns a column instead of a table. I was also thinking about placing a second filter (either a FILTER or just the ALL/ALLEXCEPT function directly) as another parameter in the CALCULATE function, but the same thing. Where and which filter do I remove to get a cumulative calculation? Should I separate it into multiple Measures? (Although I think it should be possible to make anything from multiple measures into one "nested" measure, it might be easier?)
For the fix Y axis, I guess I could add a couple of more series that take the min and max, respectively, of all the calculations for the other series, and hide them with a neutral color or something. Is it possible to remove individual series from the legend?
Can you share a dummy sample, please? That would make it easy for us to discuss this case.
So, the report view looks like this. I want it to be cumulative on the X-axis, which is the rounds. I really struggle on how to build the measure (like if it is supposed to be with SUM, SUMX, or something completely different). If I select a round through the droplist, the actual values should be unchanged, but the graph should only plot until the point that I have decided.
The roud is just an integer, like this. I specifically don't want it to be a date, so I cannot use the xTD functions for that.
And the actual data looks like this. In order to plot the entire graph at once (well, X-axis, at least), I have the "dummy serie" that just plots a 0 for each (maybe I could simplify that to just the first and the last, come think of it). It doesn't look nice, but I can live with it. Is it possible to have it like this to force it to plot the entire graph, and then somehow hide it from the legend? Also, it's not just one value for each serie and round, but a collection, which makes it perhaps more difficult. The category is not really relevant here, I should think (basically, the reason it is a collection is that each round calculates a sum of different values, which makes it important). I guess I could go back to the query editor and create a new table or something, which does some of the math to make it just one per serie and round, but I'd prefer not to, of course.
Maybe this solution. Please check out the demo in the attachment.
1. Create an independent [Round] table.
2. Create a measure.
Measure = VAR selectRound = SELECTEDVALUE ( SclierTable[Round] ) RETURN IF ( MIN ( RoundTable[Round] ) > selectRound && ISBLANK ( selectRound ) = FALSE (), 0, CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( RoundTable[Round] ), RoundTable[Round] <= MIN ( RoundTable[Round] ) ) ) )