Cumulative Median

Question

Hi Folks,&nbsp;I have a data look like below table.&nbsp;Version&nbsp; &nbsp; &nbsp; &nbsp;Weeks&nbsp; &nbsp; &nbsp; &nbsp;steps&nbsp; &nbsp; &nbsp; &nbsp;AccryV1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07-Jun-20&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70%V1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14-Jun-20&nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80%V1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21-Jun-20&nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60%V2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14-Jun-20&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80%V2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21-Jun-20&nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90%V2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28-Jun-20&nbsp; &nbsp; 3V3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21-Jun-20&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;85%V3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28-Jun-20&nbsp; &nbsp; 2V3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05-Jul-20&nbsp; &nbsp; &nbsp;3V4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28-Jun-20&nbsp; &nbsp; 1V4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05-Jul-20&nbsp; &nbsp; &nbsp;2V4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12-Jul-20&nbsp; &nbsp; &nbsp;3&nbsp;I have 4 versions of forecast avaliable with sales qty for past weeks to calculate Accuracy (Which i am calculating in data manipulation).&nbsp;I want to see accuracies like below.&nbsp;&nbsp;Versions&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Up to 1 week&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Up to 2 weeks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Up to 3 weeks&nbsp; &nbsp;V1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 70%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;75%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70%&nbsp; &nbsp;V2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 80%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;85%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;&nbsp; &nbsp;V3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 85%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp;V4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;What i basically need to do is to get median of cumulative accuracies.Like for the version V1 i will have median of step1 which will be same, median of step1 and step 2 which will be 75% (Median of 70% and 80%), and median of step 1, step2, and step3 which will be 70% (Median of 70%,80%,60%).but when we look for Version V2 i should get cumulative accuracies till step 2 only as i do not have accuracy for step 3.similarly for Version V3 i will only be having median of step 1 as i do not have step2 and step 3 for V3 version.and for V4 i will get nothing.&nbsp;can anyone hepl me to get the solution.&nbsp;Thanks in Advance.

icey · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Please check:
&nbsp;
1. Create a "Up to n weeks" table.
Up to n weeks = 
VAR t =
    GENERATESERIES ( 1, MAX ( 'Table'[steps] ) )
RETURN
    ADDCOLUMNS (
        t,
        "Up to n weeks", IF ( [Value] = 1, "Up to 1 week", "Up to " &amp; [Value] &amp; " weeks" )
    )

&nbsp;
2. Create a measure.
Up to n week = 
VAR n =
    MAX ( 'Up to n weeks'[Value] )
VAR CurrentAccry =
    CALCULATE ( SUM ( 'Table'[Accry] ), 'Table'[steps] = n )
RETURN
    IF (
        ISBLANK ( CurrentAccry ),
        0,
        CALCULATE ( MEDIAN ( 'Table'[Accry] ), 'Table'[steps] &lt;= n )
    )

&nbsp;
3. Create a Matrix visual.

&nbsp;
BTW, .pbix file attached.
&nbsp;
&nbsp;
Best Regards,
Icey
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Cumulative Median

2 Replies

Related content

Help with Median

Cumulative Total

Creating Cumulative Graphs

Year to date cumulative

Cumulative Total