cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JessVinsteen
Helper I
Helper I

Financial accounting - Sum account

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 ? 

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-08-05 155654.png

For the related .pbix file,pls see attached.

 

 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-08-05 155654.png

For the related .pbix file,pls see attached.

 

 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Arklur
Resolver II
Resolver II

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
Super User
Super User

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

 

 

 

 







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

Proud to be a Super User!




amitchandak
Super User
Super User

@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?

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.