Cumulative A B C -> 3A+2B+C

Anonymous — Tue, 23 Apr 2024 15:49:04 GMT

Hi
My data set is like this

2024 | 2025 [ 2026

A | B | C

I would like to créate a line chart whith a dax function like this :

2024 = A

2025 = A+A+B

2026 = A+A+A +B+B+C

Thanx for your help

Re: Cumulative A B C -> 3A+2B+C

barritown — Tue, 23 Apr 2024 22:00:39 GMT

Hi Anonymous,

You can try such a calculated column:

In plain text for convenience:

Result = VAR currentYear = [Year] VAR _tbl1 = FILTER ( data, [Year] <= currentYear ) VAR _tbl2 = ADDCOLUMNS ( _tbl1, "@mult", currentYear - [Year] + 1 ) VAR _tbl3 = ADDCOLUMNS ( _tbl2, "@res", [@mult] * [Value] ) RETURN SUMX ( _tbl3, [@res] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Cumulative A B C -> 3A+2B+C

sanalytics — Thu, 25 Apr 2024 10:49:52 GMT

Hello Anonymous

Below is my sort of solution for your problem.. This is little bit lengthy but can give you optimum desired result.

Step 1 :
Transform your data in PQ. Below is the complete code.

let Source = Data, #"Demoted Headers" = Table.DemoteHeaders(Source), #"Transposed Table" = Table.Transpose(#"Demoted Headers"), #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Year"}, {"Column2", "Item"}, {"Column3", "Value"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}), Year = #"Changed Type"[Year], Custom1 = #"Changed Type", #"Added Index1" = Table.AddIndexColumn(Custom1, "Index", 1, 1, Int64.Type), #"Added Index" = Table.AddIndexColumn(#"Added Index1", "Rows", List.Count(Year), -1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "GenerateRows", each List.Repeat({ [Index] },[Rows])), #"Expanded GenerateRows" = Table.ExpandListColumn(#"Added Custom", "GenerateRows"), #"Removed Other Columns" = Table.SelectColumns(#"Expanded GenerateRows",{"Year", "Item", "Value", "Index"}), #"Added Index2" = Table.AddIndexColumn(#"Removed Other Columns", "Index.1", 1, 1, Int64.Type), #"Renamed Columns1" = Table.RenameColumns(#"Added Index2",{{"Index.1", "Flag"}}) in #"Renamed Columns1"

Step 2 :
Create a Calculated Column for generating new year. Below is the DAX code.

NewYear = VAR _YearFlag = RANK( SKIP,SUMMARIZE( ALLSELECTED( 'Table' ),'Table'[Year],'Table'[Item],'Table'[Value],'Table'[Index],'Table'[Flag] ), ORDERBY( 'Table'[Flag],ASC ),,PARTITIONBY( 'Table'[Item] ) ) - 1 VAR _Result = 'Table'[Year] + _YearFlag RETURN _Result

Step 3 :

Create a cumulative measure. Below is the DAX code

Cumulative Measure = CALCULATE( SUM( 'Table'[Value] ), WINDOW( 1,ABS, 0,REL, SUMMARIZE( ALLSELECTED('Table' ),'Table'[Year],'Table'[Item],'Table'[Flag],'Table'[Value],'Table'[NewYear] ), ORDERBY( 'Table'[Flag], ASC ), PARTITIONBY( 'Table'[Item] ) ) )

Below is the output ss

Below is the attached pbix file.

https://www.transfernow.net/dl/20240425Hapf5UDK

Hope, it will help you.

Regards

sanalytics

topic Cumulative A B C -> 3A+2B+C in DAX Commands and Tips

Cumulative A B C -> 3A+2B+C

Re: Cumulative A B C -> 3A+2B+C

Re: Cumulative A B C -> 3A+2B+C