Forum Discussion

chaitanya1's avatar
chaitanya1
Helper I
1 year ago

Cummulative measure issue

I am working cohort analysis issue i have matrix table in rows i have how many customers column, feeder collection column, go live date column, serviceable feeder. In columns i have period column that is months 0 to 10 months, in values i have measure 

Month/ Quarter =
IF(SELECTEDVALUE('Cohort Combined'[Type]) = "Month", [TR Avg], [TR Sum])
TR Avg = //AVERAGE('Cohort Analysis M'[TR])
CALCULATE(AVERAGE('Cohort Combined'[TR]),'Cohort Combined'[Type] = "Month")
TR Sum =
IF(
    ISINSCOPE('Cohort Combined'[Serviceable Feeder]),
    SUMX(
        FILTER(
            'Cohort Combined',
            'Cohort Combined'[Type] = "Quarter"
        ),
        'Cohort Combined'[TR]
    ),
    AVERAGEX(
        SUMMARIZE(
            FILTER(
                'Cohort Combined',
                'Cohort Combined'[Type] = "Quarter"
            ),
            'Cohort Combined'[Actual Go-Live],
            'Cohort Combined'[Serviceable Feeder],
            'Cohort Combined'[Active Customers],
            'Cohort Combined'[Serviceable Units],
            'Cohort Combined'[Premises Ready for Service],
            'Cohort Combined'[Serviceable Pending ROE],
            'Cohort Combined'[Serviceable Approved ROE],
            "QuarterlyTR", SUM('Cohort Combined'[TR])
        ),
        [QuarterlyTR]
    )
)
I have each month avergae value coming in columns 0 to 10 months, but the request is 0th + 1st month = 1st month, 1st month + 2nd month = 2nd month...... 9th month + 10th month = 10th month but i when i tried i failed like that can you help on this please. Above measures i am using by changing that i need to achieve cummulative type right now it giving 0 to 10 months individual percent but i want 0th + 1st month = 1st month like this kind of. I hope i told everything here.
General Comment
Tips and Tricks

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi chaitanya1  - If you want to apply this cumulative logic to your existing Month/Quarter measure, you can adapt this below, slight modifications.

     

    Month/Quarter Cumulative =
    IF(
    SELECTEDVALUE('Cohort Combined'[Type]) = "Month",
    CALCULATE(
    SUM('Cohort Combined'[TR]),
    FILTER(
    ALL('Cohort Combined'[Period]),
    'Cohort Combined'[Period] <= SELECTEDVALUE('Cohort Combined'[Period])
    )
    ),
    CALCULATE(
    SUM('Cohort Combined'[TR]),
    FILTER(
    ALL('Cohort Combined'[Quarter]), -- Adjust for quarters
    'Cohort Combined'[Quarter] <= SELECTEDVALUE('Cohort Combined'[Quarter])
    )
    )
    )

     

     

    CHeck it, still issue exist share pbix file by removing the sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi chaitanya1 

     

    Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

     

     

     

     

    Best Regards,

    Jayleny