Accounts System - Templates

DAX DAX Calculations
1

In our accounts system there is a facility that allows users to define templates for the production of mainly, but not exclusively, P & L Accounts and Balance Sheets.

Our approach to templates is very different to the one shown in the various courses you offer.

Our templates allow users to define the calculation rules for sub totals and grand totals.

There is one calculation command that I am struggling with the DAX for is the one that allows users to sum a range of lines in the report.

I have attached the pbix file and if you look at the “Template - Revenue” there is a ready made template which is hopefully self explanatory.

I have created a Measure in the Profit and Loss Measures Section and the DAX measure is Revenue Data Value.

I anticipate creating a virtual table to contain the total of Nominal Account Codes from NlTransactionsPosted that are specified in the range of lines to be summed up.

I am really struggling to get that DAX syntax sorted properly and any help will be extremely welcome.

Best regards

Paul

Accounts.pbix (1.2 MB)

2

@PaulBoyes

I am not clear on which report you want to see the results but check to see if doing the SWITCH for the “Revenue Data Value” measure this way would help:

RETURN
    SWITCH (TRUE (),
        vCurrentType = "Header", "",
        vCurrentType = "Data", vCurrentValue,
	    vCurrentType = "Calculated",
	        SWITCH (TRUE (),
	            vOp1 = "Cpy", vLine2Value,
	            vOp1 = "Mul", vCurrentValue * vLine2Value,
	            vOp1 = "Div", vCurrentValue / vLine2Value,
	            vOp1 = "%", vCurrentValue / vLine2Value * 100,
		        vOp1 = "Add", vCurrentValue + vLine2Value,
		        vOp1 = "Adr", vCalcLine2,
		        0
	        ),
        0
    )

This is just the return part of the code.

3

Thanks for posting your question @PaulBoyes. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Hi,

Thank you for your reply. Yes. that is helpful.

My remaining issue is the matter of the Adr command where I need to sum up a series of sequence numbers of data lines (as specified by the Adr command) and get a single total for the account codes represented from NltransactionsPosted.

It is that DAX to create a virtual table and calculate the value that I am struggling with.

Best regards

Paul

5

Hi

I have managed to sort out the DAX for ADR calculated lines.

What I cannot see for the life of me is how to calculate totals where previous ADR calculated lines are part of another calculated line.

I have a feeling that I am asking to much of Power BI here.

Regards

Paul

6

Hi @PaulBoyes, just a friendly reminder, if your original question has been answered within the forum it is important to mark your thread as ‘solved’. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum