Combining measures

[Anonymous]

Hi,

I have 2 measures as follows,

Debit  =

SUMX (

    SUMMARIZE (

        RP_TR_TRANSACTION_DETAILS,

        RP_TR_TRANSACTION_DETAILS[As of Date],

        RP_TR_TRANSACTION_DETAILS[account_cd],

        "TotalCount", if([Sum-Amount]>0,[Sum-Amount],blank())

    ),

    [TotalCount]

)

Credit =

SUMX (

    SUMMARIZE (

        RP_TR_TRANSACTION_DETAILS,

        RP_TR_TRANSACTION_DETAILS[As of Date],

        RP_TR_TRANSACTION_DETAILS[account_cd],

        "TotalCount", if([Sum-Amount]<0,[Sum-Amount],blank())

    ),

    [TotalCount]

)

I want them to be combined into a single measure.

Can this be done?

[amitchandak]

@Anonymous , Not very clear Try like

SUMX (
SUMMARIZE (
RP_TR_TRANSACTION_DETAILS,
RP_TR_TRANSACTION_DETAILS[As of Date],
RP_TR_TRANSACTION_DETAILS[account_cd],
"TotalCount", [Sum-Amount]
),
[TotalCount]
)
or
SUMX (
SUMMARIZE (
RP_TR_TRANSACTION_DETAILS,
RP_TR_TRANSACTION_DETAILS[As of Date],
RP_TR_TRANSACTION_DETAILS[account_cd],
"TotalCount", abs([Sum-Amount])
),
[TotalCount]
)

[Anonymous]

@amitchandak 

I'll make it more clear. Created a measure

Sum-Amount = SUMX (
    SUMMARIZE (
        RP_TR_TRANSACTION_DETAILS,
        RP_TR_TRANSACTION_DETAILS[As of Date],
        RP_TR_TRANSACTION_DETAILS[account_cd],
        "TotalCount", Sum(RP_TR_TRANSACTION_DETAILS[Amount])
    ),
    [TotalCount]
)
Next i need division of this measure >0 and < 0 which should be categorized as Debit and Credit respectively. Right now i'm using 2 measures as mentioned in my previous post. Is there a way that i can calculate this Debit/Credit using a single measure?

[mahoneypat]

Here is one way that might work for you.

Credit Debit Ration =

var summarytable = SUMMARIZE (
        RP_TR_TRANSACTION_DETAILS,
        RP_TR_TRANSACTION_DETAILS[As of Date],
        RP_TR_TRANSACTION_DETAILS[account_cd],
        "TotalCount", Sum(RP_TR_TRANSACTION_DETAILS[Amount])
    )

var credit = SUMX (
    Filter(summarytable,[TotalCount] >0)
    [TotalCount]
)

var  = debit = SUMX (
    Filter(summarytable,[TotalCount] <0)
    [TotalCount]
)

return credit/debit

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@@mahoneypat @amitchandak @darlove @Greg_Deckler

Thank you for the DAX. But its not working as expected. The values are way off the actual values.

Is there any other way in which i can combine both the measures (Credit or Debit) into a single ome.

Any help would be appreciated.

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Can you share sample data and sample output. Looking at formula difficult to say now.

[Anonymous]

I'll make it more clear. Created a measure

Sum-Amount = SUMX (

    SUMMARIZE (

        RP_TR_TRANSACTION_DETAILS,

        RP_TR_TRANSACTION_DETAILS[As of Date],

        RP_TR_TRANSACTION_DETAILS[account_cd],

        "TotalCount", Sum(RP_TR_TRANSACTION_DETAILS[Amount])

    ),

    [TotalCount]

)
Next i need division of this measure >0 and < 0 which should be categorized as Debit and Credit respectively. Right now i'm using 2 measures as mentioned in my previous post. Is there a way that i can calculate this Debit/Credit using a single measure?