Creating a new table based on multiple criteria evaluations with DAX

[mterry]

I have a data table and would like to create a new table based on multiple criteria (if it's possible to do this by grouping or modifying the existing table that's fine as well). In the new table I want to summarize by the Office Column and Subcategory column and have a third column that either has 'Yes', 'No', or 'N/A' in it. If the Attribute column is First and the Percentage is 0, then the new 3rd column  should be 'N/A', otherwise for each Subcategory I need to have the Percentage amount for the row with the Second value in the Attribute column subtract the Percentage amount for the row with the First value in the Attribute column and the resulting value is greater than or equal to 0, the new third column in the new table should say 'Yes', otherwise 'No'.  Below is a sample of the data with a fourth (unneccessary) explanation column to help show the desired result.

Office	Subcategory	Attribute	Percentage	Desired Result w/Explanation
1	AB	First	0%	'N/A' - Percentage for First is 0%
1	AB	Second	0%
1	ABC	First	10%	'Yes' - Percentage for Second - Percentage for First is 0
1	ABC	Second	10%
1	ABCD	First	10%	'No' - Percentage for Second - Percentage for First is <0
1	ABCD	Second	5%
2	AB	First	10%	'Yes' - 20%-10%>=0
2	AB	Second	20%
2	ABC	First	0%	'N/A' - 0% for First
2	ABC	Second	-10%
2	ABCD	First	10%	'No' - 0%-10%<0
2	ABCD	Second	0%

[v-zhangti · Accepted solution]

Hi, @mterry 

You can try the following methods.

Column =
VAR _first =
    CALCULATE (
        MIN ( 'Table'[Percentage] ),
        FILTER (
            'Table',
            [Subcategory] = EARLIER ( 'Table'[Subcategory] )
                && [Attribute] = "First"
                && [Office] = EARLIER ( 'Table'[Office] )
        )
    )
VAR _second =
    CALCULATE (
        MIN ( 'Table'[Percentage] ),
        FILTER (
            'Table',
            [Subcategory] = EARLIER ( 'Table'[Subcategory] )
                && [Attribute] = "Second"
                && [Office] = EARLIER ( 'Table'[Office] )
        )
    )
RETURN
    IF ( _first = 0, "N/A", IF ( _second - _first >= 0, "Yes", "No" ) )

Table:

New table =
SUMMARIZE ( 'Table', 'Table'[Office], 'Table'[Subcategory], 'Table'[Column] )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mterry]

I realize that including the desired result can be a bit confusing as it doesn't show the actual layout I'm seeking - this would be the actual final output:

Office	Subcategory	Col 3
1	AB	NA
1	ABC	Yes
1	ABCD	No
2	AB	Yes
2	ABC	N/A
2	ABCD	No