Search code examples
daxmeasurecumulative-sum

DAX divide each row value with the result of a cumulative sum

I have two tables that contain the number of users of two types (see picture).

Tables

Table_1

Table_2

I have created a measure that computes the cumulative sum of the number of Users2

TotalUsers2 =
CALCULATE (
    SUM ( Users_2[Users_2] ),
    FILTER (
        ALLSELECTED ( Users_2 ),
        'Users_2'[Year_month] <= MAX ( 'Users_2'[Year_month] )
    )
)

I would like to divide each row of table 1 with the rows of TotalUsers2.

For example, for 2019_01, I have 10 Users1 and 1000 in the totalUsers2, I want to obtain the 10/1000 * 100 value.

For 2019_02, 20/1500 * 100, for 2019_03, 30/1700 * 100, and so on.

Solution

  • Just put that measure in the denominators, except you'll need to use Users_1[Year_month] instead of Users_2[Year_month] for the MAX.

    TotalUsers2 =
DIVIDE (
    SUM ( Users_1[Users_1] ),
    CALCULATE (
        SUM ( Users_2[Users_2] ),
        FILTER (
            ALLSELECTED ( Users_2 ),
            Users_2[Year_month] <= MAX ( Users_1[Year_month] )
        )
    )
)