Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Excellent! Looks like this worked. Now I need to understand the formula. Thanks again.
I have developed the below formula so far but stuck with the last line it is not working any other option I can try?
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
@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:
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
Excellent! Looks like this worked. Now I need to understand the formula. Thanks again.
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
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.
@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]) ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |