Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbiusernew
Regular 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
vik0810
Resolver V
Resolver V

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


@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
Icey
Community Support
Community Support

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
vik0810
Resolver V
Resolver V

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.


@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

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

Grambi
Frequent Visitor

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)

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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