Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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 @Anonymous ,

 

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!
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
Community Champion
Community Champion

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

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

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.