Running Total without Date Column and with un-summarized data

[BirajDeb]

Hi all,

Thanks for checking my issue. I appreciate your time and effort.

I have a table like this.

I need a running total of the last column which should adjust based on my selection on first column slider.

The chart table summarizes the ‘Stock Invested In’ Column.

Thanks for your help.

[v-yiruan-msft · Accepted solution]

Hi @BirajDeb ,

I updated your sample pbix file(see attachment), please check whether that is what you want. Please update the formula of measure [Running Total] as below:

Running Total = 
VAR Rankingtemp =
    RANKX (
        ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
        RANKX (
            ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
            CALCULATE ( SUM ( 'Fact Underlying Portfolio'[Holding Market Value] ) )
        )
            + DIVIDE (
                RANKX (
                    ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                    CALCULATE ( VALUES ( 'Fact Underlying Portfolio'[Stock Invested in] ) ),
                    ,
                    ASC
                ),
                COUNTROWS ( ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ) ) + 1
            )
    )
VAR Accumulatedtemp =
    CALCULATE (
        SUM ( 'Fact Underlying Portfolio'[Holding Market Value] ),
        FILTER (
            ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
            RANKX (
                ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                RANKX (
                    ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                    CALCULATE ( SUM ( 'Fact Underlying Portfolio'[Holding Market Value] ) )
                )
                    + DIVIDE (
                        RANKX (
                            ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                            CALCULATE ( VALUES ( 'Fact Underlying Portfolio'[Stock Invested in] ) ),
                            ,
                            ASC
                        ),
                        COUNTROWS ( ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ) ) + 1
                    ),
                ,
                DESC
            ) >= Rankingtemp
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
        Accumulatedtemp,
        SUM ( 'Fact Underlying Portfolio'[Holding Market Value] )
    )

In addition, you can refer the solution in the following thread to get it.

Creating cumulative measure based on dynamic ranking / Dynamic running total/pareto diagram-measure

Best Regards

[BirajDeb]

I have developed the below formula so far but stuck with the last line it is not working any other option I can try?

Measure =

VAR _temptable =

CALCULATETABLE(

SUMMARIZE(

'Fact Underlying Portfolio',

'Fact Underlying Portfolio'[Stock Invested in],

"Total Holding Value", SUM('Fact Underlying Portfolio'[Holding Market Value])

),

FILTER(

ALLSELECTED('Fact Underlying Portfolio'),

ISBLANK('Fact Underlying Portfolio'[MoneyControl Name]) = FALSE()

)

)

VAR _temptable2 =

ADDCOLUMNS(

_temptable,

"IndexRank", RANKX(_temptable,[Total Holding Value],[Total Holding Value],1)

)

RETURN

CALCULATE(

SUMX(

_temptable2,

[Total Holding Value]

),

FILTER(

_temptable2,

[IndexRank]<=MAX([IndexRank])

)

)

[v-yiruan-msft]

Hi @BirajDeb ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Add one index column in Power Query Editor as below screenshot

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdA7CsQwDATQu6hO4ZH/ZwmuUoWQpNi9P4sTkLVyZ3geIc260rldDrTQ53tvx/6+EZna8hob4zLMK+v/UINYMMbeiUWbS14sKevz4cfMbIxTFCvKwpPLYlVZ/N8Tzhgn2ROwOYwcTznpBbqXfg/nYWEy2RO6l34rQ3qB7qU8fVZq7Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MoneyControl Name" = _t, #"Stock Invested in" = _t, #"Holding Market Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MoneyControl Name", type text}, {"Stock Invested in", type text}, {"Holding Market Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Stock Invested in", Order.Ascending}, {"Holding Market Value", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Stock Invested in"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"MoneyControl Name", "Holding Market Value", "Index"}, {"MoneyControl Name", "Holding Market Value", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Holding Market Value", Int64.Type}, {"Index", Int64.Type}, {"MoneyControl Name", type text}, {"Stock Invested in", type text}})
in
    #"Changed Type1"

2. Create a measure as below to get the culmulative values per stock invested in category

Measure = 
SUMX (
    FILTER (
        ALLSELECTED ( 'Fact Underlying Portfolio' ),
        'Fact Underlying Portfolio'[Stock Invested in]
            = SELECTEDVALUE ( 'Fact Underlying Portfolio'[Stock Invested in] )
            && 'Fact Underlying Portfolio'[Index]
                <= SELECTEDVALUE ( 'Fact Underlying Portfolio'[Index] )
    ),
    [Total Holding Value]
)

If the above ones can't help you, please provide some sample data in table "Fact Underlying Portfolio" with Text format(exclude sensitive data) and your expected result with calculation logic and special examples. Thank you.

Best Regards

[BirajDeb]

@v-yiruan-msft thank you for your time. The problem is the rank will change based on the moneycontrol name you select becasue the holding value changes. I am adding a sample pbi file and data. Please keep in mind that the value should be sorted. Modify the Running Tool Measure, if you are using the PBI file.

Link to the Onedrive folder:

https://1drv.ms/u/s!AjEmlqKsnXAHpQuM5WUQhb5lD63F?e=OgnMsd

[v-yiruan-msft · Accepted solution]

Hi @BirajDeb ,

I updated your sample pbix file(see attachment), please check whether that is what you want. Please update the formula of measure [Running Total] as below:

Running Total = 
VAR Rankingtemp =
    RANKX (
        ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
        RANKX (
            ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
            CALCULATE ( SUM ( 'Fact Underlying Portfolio'[Holding Market Value] ) )
        )
            + DIVIDE (
                RANKX (
                    ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                    CALCULATE ( VALUES ( 'Fact Underlying Portfolio'[Stock Invested in] ) ),
                    ,
                    ASC
                ),
                COUNTROWS ( ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ) ) + 1
            )
    )
VAR Accumulatedtemp =
    CALCULATE (
        SUM ( 'Fact Underlying Portfolio'[Holding Market Value] ),
        FILTER (
            ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
            RANKX (
                ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                RANKX (
                    ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                    CALCULATE ( SUM ( 'Fact Underlying Portfolio'[Holding Market Value] ) )
                )
                    + DIVIDE (
                        RANKX (
                            ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
                            CALCULATE ( VALUES ( 'Fact Underlying Portfolio'[Stock Invested in] ) ),
                            ,
                            ASC
                        ),
                        COUNTROWS ( ALLSELECTED ( 'Fact Underlying Portfolio'[Stock Invested in] ) ) + 1
                    ),
                ,
                DESC
            ) >= Rankingtemp
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Fact Underlying Portfolio'[Stock Invested in] ),
        Accumulatedtemp,
        SUM ( 'Fact Underlying Portfolio'[Holding Market Value] )
    )

In addition, you can refer the solution in the following thread to get it.

Creating cumulative measure based on dynamic ranking / Dynamic running total/pareto diagram-measure

Best Regards

[BirajDeb · Accepted solution]

Excellent! Looks like this worked. Now I need to understand the formula. Thanks again.

[v-yiruan-msft]

Hi @BirajDeb ,

According to your description, it seems that you want to group by [Stock Invested in] and then dynamically display the total Holding Market value based on the selected [MoneyControl Name]. Since this needs to be a dynamic value, as @amitchandak said, we need to create a measureto get it instead of calculated column. You can review the following blog to understand the difference between a calculated column and a measure.

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

---

The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

---

Please create a measure as below to get it:

Measure =
VAR _selmcontrol =
    ALLSELECTED ( 'Table'[MoneyControl Name] )
VAR _selsinvested =
    SELECTEDVALUE ( 'Table'[Stock Invested in] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Holding Market Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Stock Invested in] = _selsinvested
                && 'Table'[MoneyControl Name] IN _selmcontrol
        )
    )

Best Regards

[BirajDeb]

Thanks @v-yiruan-msft and @amitchandak these are close to the solution but not exactly  the solution. If you closely look at the data you will see col3 (stock invested in) has various vlaues for each of the fund (col1). So based on the user selection of the fund the overall ranking of the col3 changes. So when I sort by value the running total does not match for each line though the total maches. I am trying to develop a formula which l will post after this. Please check once.

[amitchandak]

@BirajDeb , for that you need to create a measure if you want the calculation to change based on selection

add an index column in power query , then create a measure

calculate(sum(Table[Latest Market Value]), filter(Allselected(Table), [Index] = max(Table[Index]) ))