Column to Measure

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

 

 

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

Rev sum: = 
SUM( Sales[Revenue] )

 

Cumulate Rev sum: = 
CALCULATE (
    SUM ( Sales[Revenue] ),
    WINDOW (
        1,
        ABS,
        0,
        REL,
        SUMMARIZE ( ALL ( Sales ), Category[Category], 'Calendar'[Date] ),
        ORDERBY ( 'Calendar'[Date], ASC ),
        ,
        PARTITIONBY ( Category[Category] )
    )
)

 

Category Rev total: = 
IF (
    NOT ISBLANK ( [Rev sum:] ),
    CALCULATE ( [Rev sum:], ALL ( 'Calendar'[Date] ) )
)

 

% cumulate rev: = 
VAR _cumulaterev =
    CALCULATE (
        SUM ( Sales[Revenue] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE ( ALL ( Sales ), Category[Category], 'Calendar'[Date] ),
            ORDERBY ( 'Calendar'[Date], ASC ),
            ,
            PARTITIONBY ( Category[Category] )
        )
    )
VAR _categorytotalrev =
    CALCULATE (
        SUM ( Sales[Revenue] ),
        WINDOW (
            1,
            ABS,
            -1,
            ABS,
            SUMMARIZE ( ALL ( Sales ), Category[Category], 'Calendar'[Date] ),
            ORDERBY ( 'Calendar'[Date], ASC ),
            ,
            PARTITIONBY ( Category[Category] )
        )
    )
RETURN
    DIVIDE ( _cumulaterev, _categorytotalrev )