Wednesday, March 22, 2023

DAX - Running total based on ranking index - Acumulado Ranking

Rank = 


RANKX (

    SUMMARIZE (

        ALLSELECTED ( FAT_TABLE ),

        FAT_TABLE[YYYYMM],

        FAT_TABLE[VAR1]

    ),

    CALCULATE ( SUM ( FAT_TABLE[Price (€/t)] ) ),

    ,

    ASC,

    DENSE

)

=============================================== 


Running VAR_Sum = 

IF (

    ISINSCOPE ( FAT_TABLE[VAR1] ) && ISINSCOPE ( FAT_TABLE[YYYYMM] ),

    VAR CurrentRank = [Rank]

    VAR SummarizeFAT_TABLE =

        CALCULATETABLE (

            SUMMARIZE ( FAT_TABLE, FAT_TABLE[VAR1], FAT_TABLE[YYYYMM] ),

            ALLSELECTED ( FAT_TABLE )

        )

    VAR FAT_TABLESales =

        ADDCOLUMNS ( SummarizeFAT_TABLE, "@TotalSum", [VAR_Sum], "@Rank", [Rank] )

    VAR Result =

        SUMX ( FILTER ( FAT_TABLESales, [@Rank] <= CurrentRank ), [@TotalSum] )

    RETURN

        Result

)

=============================================== 


No comments:

Post a Comment

Power BI APP - Delete or Unpublish

 UNPUBLISH DELETE