cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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?
7 REPLIES 7
Super User

@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
Not applicable

@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?

Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Anonymous
Not applicable

@@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.

Community Support

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.

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.

Super User

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

Anonymous
Not applicable

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors