Running Average with measure and no date

[jnd27]

Hi, I have 2 data tables and a date index table which is as followed: January | 1 February | 2 March | 3 ... December | 12 those 2 tables are connected by the above index numbers. Those tables have $ values and an index column as well that I made to make the relationship. My question is how I can do a running ratio average, a measure made in a separate measures table. For example:
Table 1
1 | $500 2 | $300 3 | $100
Table 2
1 | $200 2 | $400 3 | $0
I want a line chart showing running averages of the $ values by index in a calculation of Table1 divided by Table2 the values I want would be 5/2, then 8/6 --> (500+300/(200+400)), and 3 would be 9/6 --> (500+300+100)/(200+400+0) I have tried a bunch of online solutions but none have both of the conditions of using a a numeric index and using a custom measure in the running average, I was able to do a line but not able to add in the measure through VARs+calculate+sumx. Thanks so much

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Expected result measure: =
VAR _dataonerunningtotal =
    CALCULATE (
        SUM ( 'Data one'[Value one] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'Month'[Month], 'Month'[Index] ),
            ORDERBY ( 'Month'[Index], ASC )
        )
    )
VAR _datatworunningtotal =
    CALCULATE (
        SUM ( 'Data two'[Value two] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'Month'[Month], 'Month'[Index] ),
            ORDERBY ( 'Month'[Index], ASC )
        )
    )
RETURN
    DIVIDE ( _dataonerunningtotal, _datatworunningtotal )

[jnd27]

Thanks Jihwan, sadly I don't have access to the version of power bi with window, index, rel,orderby. Is there a way to translate that? Thanks again

[Jihwan_Kim · Accepted solution]

Hi,

In the sample pbix file, please try to write a measure something like below.

Expected result measure V2: =
VAR _currentmonth =
    MAX ( 'Month'[Index] )
VAR _dataonerunningtotal =
    CALCULATE (
        SUM ( 'Data one'[Value one] ),
        FILTER ( ALL ( 'Month' ), 'Month'[Index] <= _currentmonth )
    )
VAR _datatworunningtotal =
    CALCULATE (
        SUM ( 'Data two'[Value two] ),
        FILTER ( ALL ( 'Month' ), 'Month'[Index] <= _currentmonth )
    )
RETURN
    DIVIDE ( _dataonerunningtotal, _datatworunningtotal )

[jnd27]

Sorry for the late reply but this worked great and saved me so much time, thanks! 😊