Cumulative Median

[Anonymous]

Hi Folks,

I have a data look like below table.

Version       Weeks       steps       Accry
V1              07-Jun-20    1             70%
V1              14-Jun-20    2             80%
V1              21-Jun-20    3             60%
V2              14-Jun-20    1             80%
V2              21-Jun-20    2             90%
V2              28-Jun-20    3
V3              21-Jun-20    1             85%
V3              28-Jun-20    2
V3              05-Jul-20     3
V4              28-Jun-20    1
V4              05-Jul-20     2
V4              12-Jul-20     3

I have 4 versions of forecast avaliable with sales qty for past weeks to calculate Accuracy (Which i am calculating in data manipulation).

I want to see accuracies like below.

Versions          Up to 1 week           Up to 2 weeks          Up to 3 weeks
   V1                      70%                           75%                         70%
   V2                      80%                           85%                          0 
   V3                      85%                            0                              0
   V4                      0%                              0                              0

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.

can anyone hepl me to get the solution.

Thanks in Advance.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

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 " & [Value] & " weeks" )
    )

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

3. Create a Matrix visual.

BTW, .pbix file attached.

Best Regards,

Icey

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

[Anonymous]

Hi Icey,

It worked for me.

Thanks a lot for the quick solution.