cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zindagi414
Regular Visitor

Subtract rows based on columns that have same values

 

Here is what I would like to do. For example, lets say I have a chart as below:

Food Type Store Type Value
Sandwich Subway Type1 7
Sandwich Subway Type2 5
Taco Qdoba Type1 10
Taco Qdoba Type2 5
Taco Qdoba Type3 6

 

I would like to create a new column in this row. For each row, I want to Filter the SAME "Food Type" and "Store" and take the "Value" difference between "Type #" and "Type 1. 

 

In the table below, I have added the "Desired Value" I'd like to see being done. I also added a couple of Columns ("Row" and "Formula" to show how I'd get to the "Desired Value"

 

Row # Food Type Store Type Value Desire Values Formula
1 Sandwich Subway Type1 7 0 Row 1 - Row 1
2 Sandwich Subway Type2 5 2 Row 1 - Row 2
3 Taco Qdoba Type1 10 0 Row 3 - Row 3
4 Taco Qdoba Type2 5 5 Row 3 - Row 4
5 Taco Qdoba Type3 6 4 Row 3 - Row 5

 

In advance, any assistance will be greatly appreciated. Thanks! 

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @zindagi414 

If it's a calculated column that you are after:

 

New column =
Table1[Value]
    - CALCULATE (
        MAX ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Food Type], Table1[Store] ),
        Table1[Type] = "Type1"
    )

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User III
Super User III

@zindagi414 

Good question. The filters in the calculate will filter the table down to one single row. So the MAX is not strictly needed. You could just as well use DISTINCT ( ) 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

AlB
Super User III
Super User III

Hi @zindagi414 

If it's a calculated column that you are after:

 

New column =
Table1[Value]
    - CALCULATE (
        MAX ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Food Type], Table1[Store] ),
        Table1[Type] = "Type1"
    )

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

zindagi414
Regular Visitor

@AlB  - thank you! This works as expected!

 

For my own knowledge/learning, can you clarify why the MAX function is needed here? It works with MAX, but why does the code need to look at the maximum value?

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors