Need DAX formula to calculate valued based on conditions in a Dimension Table

DAX DAX Calculations
1

I am preparing a financial report on the basis of the Trial Balance and GL Masterdata. In our organization the amount reflected in the GLs is shown in either Receipt and Payment side based on the category of the GL shown in the GL Masterdata.

There are three categories of the GLs,

  1. Receipt - These GLs will only be shown in Receipt Side. Therefore if any Debit amount reflecting in the GL then the debit amount is deducted from Credit amount and net amount will be shown in Receipt Side.

  2. Payment = These GLs will only be shown in Payment Side. Therefore if any Credit amount reflecting in the GL then the credit amount will be deducted from the Debit amount and net amount will be shown in Payment side.

  3. Both = The Credit amount reflecting in these GLs will be shown in Receipt Side and Debit amount will be shown in Payment Side.

I have to prepare DAX formula for Receipt and Payment for the report. But I am not able to do it. I am getting boolean result if I use nested IF formula with SELECTEDVALUES.

This is the GL Masterdata table,

image

and there are two DAX Measure one is SAP Credit and the other is SAP Debit.

2

Hi @Abhijeet357 ,

I strongly advice to use measure branching (use one measure on the top of another) to split this task in several small/ easier steps.

I create small example and create lots of other measures so I can better explain.

First Sum Credit, Debit mesures

then Create measures Saldo C-D and Saldo D-C on the top of that

for
“Debit amount reflecting in the GL then the debit amount is deducted from Credit”

"Credit amount reflecting in the GL then the credit amount will be deducted from the Debit amount "

Saldo C_D = [C_Credits]-[D_Debits] 
Saldo D_C = [D_Debits] -[C_Credits]

Then Create Receipt and Payment on the Type Level (GL Account Level )

Receipt by Type= 
VAR _CurrType = SELECTEDVALUE( Sheet1 [Type] ) 

RETURN
CALCULATE(
    SUMX(
      'Sheet1',
        SWITCH(
            TRUE(),
            _CurrType = "Receipt", [Saldo C_D],
            _CurrType = "Both", [C_Credits],
            0
        )
    ),
    FILTER(
        Sheet1,
        Sheet1 [Type] = "Receipt" ||
        Sheet1 [Type] = "Both"
    ))

Similar for Payment

Payment by Type = 
VAR _CurrType = SELECTEDVALUE( Sheet1 [Type] ) 

RETURN
CALCULATE(
    SUMX(
      'Sheet1',
        SWITCH(
            TRUE(),
            _CurrType = "Payment", [Saldo D_C],
            _CurrType = "Both", [D_Debits],
            0
        )
    ),
    FILTER(
        Sheet1,
        Sheet1 [Type] = "Payment" ||
        Sheet1 [Type] = "Both"
    )
)

I use 0 as Other options - usually I use Other Options since I always see some missing types in accounting data.

And Then Sum on top of that to get Receipt, Payment that you looking for.

In Excel/ PBI Example I create simple Example and use Sheet1 and Acc 11, Acc 21 etc.(for GL accounts)

Example_Abhijeet357.pbix (21.3 KB)

GL_sim.xlsx (8.8 KB)

Hoping you find this useful and meets your requirements that you’ve been looking for.
Good luck with your financial report

3

Thanks a lot for the solution.
So for this to work I have to insert the Type column in the trial balance report (Through query merge in powerquery?)

4

Hi @Abhijeet357 ,

I try to replicate Type from your picture - I supposed that is a category mentioned at:

If you have that category that divide Receipt / Payment /Both in other column you can replace that column instead Sheet1[Type]

Receipt and Payment are total values so can exist also as separate measures :

image
image923×631 39.7 KB

It’s much easier to provide solution with small sample in pbix file.

If you need further explanation, please let me know.

Good luck

5

Thanks a lot . It is working now. I just need to fine tune it for using less resources.

1 Like