Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Feilin
Helper II
Helper II

Cumulative in a graph

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?

3 REPLIES 3
v-jiascu-msft
Employee
Employee

Hi @Feilin,

 

Can you share a dummy sample, please? That would make it easy for us to discuss this case.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ok, sure:

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.

 

 

Dummy 1.png

 

 

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.

Dummy 2.png 

 

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.

 Dummy 3.png

 

 

 

 

Hi @Feilin,

 

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] )
            )
        )
    )

Cumulative_in_a_graph

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.