Chart of Accounts Totalling

[Anonymous]

Is it possible to recreate this Chart Of Accounts in DAX?

As you can see this is a list of accounts starting at 1000 with a heading called Statement of Financial Position that I would like to also have returned into a visual with the related subaccounts that all roll up.

The "account type" indicates if its a Heading, Begin-Total or End-Total but the trick is to use the totalling column to calculate the range to sum e.g. Cash and Equivalents are the sum of 1200 .. 1299 as indicated below and follows on to the rest of the items. See 2nd picture.

What Visual would best be used here?

Sample PIBX here

Sample Data in Excel 

[Zosy]

Hi! Could you share your file again if possible? I am facing the same issue as you, but I do not understand much of the solution without the pbx file.
Thank you

[stevedep]

Hi,

Its possible but requires some trickery. 

First create new columns for Level 1, 2, etc. Create a hierarchy from them. Put them in the rows of a matrix. The columns will be dynamic. First store the column name in a separate table. Join the table using the value 1. Add a switch statement for the value part of the matrix:

ColValue = SWITCH([currentcol];"Account Category";MAX(Chart_of_Accounts[Account_Category]);"Account Subcategory";MAX(Chart_of_Accounts[Account_Category]);"Balance";SUM(Chart_of_Accounts[Balance]))

See this video with the steps:

Link to the Power BI file is here. 

Please mark as solution when this is what you are looking for. Kudo's are appreciated.

Kind regards,

Steve. 

[Anonymous]

Hi Steve

Thanks for assisting. 

Q.1 How did you create the L1 and L2 index? Was this manually created?

Whilst it seems that you have created what I was lookng for, I was hoping to make use of the totalling column as this has a number range to utilise eg 1200..1299, which can change occasionally and I dont want to have to create new indexes each time a new General Ledge Number is added.

I needed to replicate this chart of accounts giving a total for the grouped totals eg total of 1200..1299 being a subtotal when the Account Type is End-Total. 

I hope that makes sense. 

[stevedep]

Hi,

The L1 is the first left character of the Account No. L2 is the first 2. So this is robust, in the sense that you do not need to manually add new levels.

Regards, Steve. 

[stevedep]

Does this work for you?

[stevedep]

Kindly let us know if this works for you. Thanks!

[Anonymous]

Hi @stevedep ,

I really appreciate your taking the time to respond to my query. Your PBIX file looks very promising.

I am studying your PBIX file and trying to replicate the steps for my scenario .  Will get back to you shortly.

Thank you.

[stevedep]

Welcome, good luck. I will see a notification when you tag me.

[Anonymous]

Hi Stevedep

i have not attempted to do this report using the manner you suggested. I am trying to work out of it possible to use the existing data and use the totalling column to calculate the data. I don't want to make any changes to the data structure as this is how business central send out the data.

thank you 

[stevedep]

Hi,

Just to be sure, I only added 2 calculated columns, so there are no changes to the data model. Its all done in the front-end.

Kind regards, Steve. 

[stevedep]

@Anonymous ; Have you been able to take a look? It seems pretty clear its working from the visuals I shared?

[Anonymous]

HI Stevedep

If you can bring into your visalisation the totalisaion column and then scroll down to No. 1299 you can see that the field says 1200..1299. The idea is to use this totalling columnn to calculate the sum of the No (so 1200 through 1299) to get your the total value for Total Cash and Equilivants.

Another example is 2499 which is Total Motor Vehicle. To work out the calculuation of the amounts you need to add all the general ledger accounts from 3400..3499. This would be another ledger balance. and so on.

I hope that makes sense.

[stevedep]

Hi @Anonymous ,

I see the requirement now for dynamic totaling based on the account range in the totaling column. I was thrown a bit 'off balance' 🙂 because the value was already available in your dataset, and I focussed on the lay out challenge too much. 

Anyways, should be working now as expected with the following DAX code:

DynamicBalance = 
var _Start = SELECTEDVALUE(Chart_of_Accounts[start])
var _End = SELECTEDVALUE(Chart_of_Accounts[end])
return
CALCULATE(SUM(Chart_of_Accounts[Balance]);FILTER(ALL(Chart_of_Accounts);Chart_of_Accounts[No]>=_Start && Chart_of_Accounts[No] < _End && Chart_of_Accounts[Totaling]=""))

Which requires 2 columns to give the start and end of the range:

start = 
IF(SEARCH("..";Chart_of_Accounts[Totaling];1;0)>0;
LEFT(Chart_of_Accounts[Totaling];
SEARCH("..";Chart_of_Accounts[Totaling];1;0)-1);BLANK())

end = 
IF(SEARCH("..";Chart_of_Accounts[Totaling];1;0)>0;
RIGHT(Chart_of_Accounts[Totaling];4);BLANK())

 result:

File here. 

Hope this works for you.

Kind regards, Steve. 

[Anonymous]

Look great! Thank you.

Sorry about the confusion. Explaining it difficult but I think you got it now. I presume we can drop the "L1","L2", ID and cols table to have a final clean solution? 

[stevedep]

@Anonymous , and now to drive it to completion and bring it together I modified the formula a bit so detailed posting are displayed, as well are the range aggrates:

DynamicBalance = 
var _Start = SELECTEDVALUE(Chart_of_Accounts[start])
var _End = SELECTEDVALUE(Chart_of_Accounts[end])
return
IF(MIN(Chart_of_Accounts[Totaling])="";CALCULATE(SUM(Chart_of_Accounts[Balance]));
CALCULATE(SUM(Chart_of_Accounts[Balance]);FILTER(ALL(Chart_of_Accounts);Chart_of_Accounts[No]>=_Start && Chart_of_Accounts[No] < _End && Chart_of_Accounts[Totaling]=""))
)

The methodology to display attributenames in matrix cells is still in (optionally) which requires:

ColValue = SWITCH([currentcol];"Name"; MAX(Chart_of_Accounts[Name]);"Account Category";MAX(Chart_of_Accounts[Account_Category]);"Account Subcategory";MAX(Chart_of_Accounts[Account_Category]);"Balance";[DynamicBalance])

  And an addition table with columns names to display the attribute values in matrix cells:

To add these column names, go to menu in the UI and 'enter data'. 

Now the final result looks like this:

This has both dynamic totaling based on the range in a cell and the layout with grouping on account no's. 

The link to the file.

Hope we got it?

[stevedep · Accepted solution]

@Anonymous , some fine tuning, prevent summing the sums twice, to prevent summing of the total account. 

Colvalue is now:

ColValue = SWITCH([currentcol];"Name"; MAX(Chart_of_Accounts[Name]);"Account Category";MAX(Chart_of_Accounts[Account_Category]);"Account Subcategory";MAX(Chart_of_Accounts[Account_Category]);"Balance";[Measure])

its referring to 'measure':

Measure = IF(HASONEVALUE(Chart_of_Accounts[No]);[DynamicBalance];SUMX(CALCULATETABLE(Chart_of_Accounts;Chart_of_Accounts[Totaling]="");[DynamicBalance]))

Dynamic balance now supports the second range:

DynamicBalance = 
var _Start = SELECTEDVALUE(Chart_of_Accounts[start])
var _End = SELECTEDVALUE(Chart_of_Accounts[end])
var _2ndstart = SELECTEDVALUE(Chart_of_Accounts[2ndstart])
var _2ndend = SELECTEDVALUE(Chart_of_Accounts[2ndend])
return
IF(MIN(Chart_of_Accounts[Totaling])="";CALCULATE(SUM(Chart_of_Accounts[Balance]));
CALCULATE(SUM(Chart_of_Accounts[Balance]);FILTER(ALL(Chart_of_Accounts);Chart_of_Accounts[No]>=_Start && Chart_of_Accounts[No] < _End && Chart_of_Accounts[Totaling]=""))+
IF(MIN(Chart_of_Accounts[secondrange])="";0;
CALCULATE(SUM(Chart_of_Accounts[Balance]);FILTER(ALL(Chart_of_Accounts);Chart_of_Accounts[No]>=_2ndstart && Chart_of_Accounts[No] < _2ndend && Chart_of_Accounts[Totaling]="")))
)

This does require 3 calculated columns:

secondrange = 
IF(SEARCH("|";Chart_of_Accounts[Totaling];1;-1)>0;
RIGHT(Chart_of_Accounts[Totaling];LEN(Chart_of_Accounts[Totaling])-SEARCH("|";Chart_of_Accounts[Totaling];1;0));"")

2ndstart = IF(LEN(Chart_of_Accounts[secondrange])>2;LEFT(Chart_of_Accounts[secondrange];4);"")

2ndend = IF(LEN(Chart_of_Accounts[secondrange])>2;RIGHT(Chart_of_Accounts[secondrange];4);"")

Now the results look like this:

Link to the file is the same.

Should be it right? Multiple ranges, proper aggregation, flexible slice and dicing. Layout in the desired shape. 

Need to wrap this one up, pro bono ours almost depleted for this one.

Thanks, 

Kind regards, Steve.