cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
powerbiusernew Frequent Visitor
Frequent Visitor

Need help with substraction within a column

Hi all, 

 

I'm a new user here, require some help. I have a column with the following values. What I would like to do is to substract the total count of "Return" to the total count of "Loan". How do I go about doing that? I've been trying all ways and nothing seems to cut it.

 

table1.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
vik0810 Member
Member

Re: Need help with substraction within a column

A simple way would be following measure:

 

Measure =
CALCULATE (
    SUM ( table[value] ), table[operation_type_desc (groups)] = "Return"
)
    - 
CALCULATE ( SUM ( table[value] ), table[operation_type_desc (groups)] = "Loan" )

If your table is filtered with the slicers, you may need to adjust the measure to manipulate filter context.

View solution in original post

powerbiusernew Frequent Visitor
Frequent Visitor

Re: Need help with substraction within a column


@vik0810 wrote:

A simple way would be following measure:

 

Measure =
CALCULATE (
    SUM ( table[value] ), table[operation_type_desc (groups)] = "Return"
)
    - 
CALCULATE ( SUM ( table[value] ), table[operation_type_desc (groups)] = "Loan" )

If your table is filtered with the slicers, you may need to adjust the measure to manipulate filter context.


Awesome! I only needed to make minor adjustment (instead of SUM, I used Count) and it works. Thank you!

 

Regards

E

View solution in original post

5 REPLIES 5
Grambi Frequent Visitor
Frequent Visitor

Re: Need help with substraction within a column

I'm not sure I understand correctly. Is this what you're looking to do? 

 

If you want to count the number of records of Type Loan and subtract the number of records of Type Return, you would create a column like this. A row with Loan gets a value of 1, a row with a Return gets a value of -1, everything else has 0 impact. 

 

 

YourColumnName = if([operation_type_desc (groups)] = "Loan";1;if([operation_type_desc (groups)]= "Return";-1;0))

You can now use this column to display the value of (number of Loans -/- number of Returns)

 

 

 

vik0810 Member
Member

Re: Need help with substraction within a column

A simple way would be following measure:

 

Measure =
CALCULATE (
    SUM ( table[value] ), table[operation_type_desc (groups)] = "Return"
)
    - 
CALCULATE ( SUM ( table[value] ), table[operation_type_desc (groups)] = "Loan" )

If your table is filtered with the slicers, you may need to adjust the measure to manipulate filter context.

View solution in original post

Icey New Contributor
New Contributor

Re: Need help with substraction within a column

Hi @powerbiusernew ,

 

Is this problem sloved?
 
If it is sloved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
 
If not, please let me know.
 
Best Regards
Icey
powerbiusernew Frequent Visitor
Frequent Visitor

Re: Need help with substraction within a column


@vik0810 wrote:

A simple way would be following measure:

 

Measure =
CALCULATE (
    SUM ( table[value] ), table[operation_type_desc (groups)] = "Return"
)
    - 
CALCULATE ( SUM ( table[value] ), table[operation_type_desc (groups)] = "Loan" )

If your table is filtered with the slicers, you may need to adjust the measure to manipulate filter context.


Awesome! I only needed to make minor adjustment (instead of SUM, I used Count) and it works. Thank you!

 

Regards

E

View solution in original post

Icey New Contributor
New Contributor

Re: Need help with substraction within a column

Hi @powerbiusernew ,

Glad to hear that. Please accept your solution above so that people who may have the same question can get the solution directly.

 

Best Regards,
Icey

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 155 members 1,748 guests
Please welcome our newest community members: