Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Combining measures

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?
Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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