Financial accounting - Sum account

[JessVinsteen]

Hey there,

We have a problem with figuring out how to calculate a total sum of our "sum accounts".

Right now we have a table with "sum accounts no." which contains values such as:

"11100.11199" which means that the accounts in that range is the reason for the total calculation..

Then we have a table with all the accounts that are within that range. 

11100, 11101, 11102 etc. 

Is there any "easy" way to calculate a total, without creating a new column with List.numbers and then do a sum with them all ? 

[v-kelly-msft · Accepted solution]

Hi @JessVinsteen ,

First split the "sum accounts no." column to 2 columns,then create a measure similarly as below:

Measure = 
CALCULATE(SUM('Table (2)'[Value]),FILTER(ALL('Table (2)'),'Table (2)'[No.]>=MAX('Table'[sum accounts no - Copy.1])&&'Table (2)'[No.]<=MAX('Table'[sum accounts no - Copy.2])))

And you will see:

For the related .pbix file,pls see attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Arklur]

As other said, you should split the column then get Account within the range, eventually creaing a bridge table. Here is a sample code for doing so:

// FinancialAccounting
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0NDDQA5KWlkqxOkA+iGdkAGGbGugZGQDZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SumAccountsNo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SumAccountsNo", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "SumAccountsNo", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"AccountNoLow", "AccountNoHigh"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"AccountNoLow", Int64.Type}, {"AccountNoHigh", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Numbers([AccountNoLow], [AccountNoHigh])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

[FarhanAhmed]

You should create a new columns and split these values to StartAccount and EndAccount

Based on those new columns you should create your SUM measures by filtering accounts

[amitchandak]

@JessVinsteen , as this a range, I do see an easy way out. Some calculation needs to be done.

Can you share sample data and sample output in table format?

[JessVinsteen]

Unfortunately Im not able to post any power bi files here, due to coporate rules...