Calculated Columns that relate to like row grouping.

[Anonymous]

Team,

 I am attempting to create a group attribute with the following data sample.  My data is based off of Sales Orders (SO's).  SO's are associated in groups by their "DEAL Number".  I'm looking to create visualations and list views based off of a DEALs status as it relates to being COMPLETED and it's respective SO's being FULLY POSTED from a billing perspective.   A deal is not Complete until all SO's are complete, and a DEAL is not FULLY POSTED until all SO's are fully posted.  I have three images below, one that shows an example of my raw data set.  One of my desired structure with the two new columns as it relates to the overall DEAL status, and one with the desired DEAL MATRIX visualation.  I thought I had this solved with the following string, however when my users attempt to filter by clicking on a DEAL in the DEAL MATRIX, the list view gives them the respective SO's in the deal but I'm not getting the overall DEAL status's stripped across all the SO's.  I am clearly missing something that will maintain the relationship to the overall DEAL Status in the new calculated columns.

Example Raw Data:

Desired Column/Table Ouptut 

New Matrix for Operations Team to Manage Deals

Current usage that got me what I thought I needed, and was giving me what I wanted in the MATRIX, but was showing me too much in my new list view due to not applying the correct desired Status Relationships.

Deal Closed Multi Partial = SWITCH (
TRUE (),
ISBLANK ( DataSet[Deal Number] ), BLANK (),
CALCULATE (
COUNTROWS ( DataSet ),
DataSet[Status] = "Closed Multi-Partial"
) < 1, "Not Multi-Partial",
"Multi-Partial"

Thank you in advance for helping me close this gap!

Scott Moody

[ChrisMendoza]

@Anonymous -

Are you also wanting to create another table to use for a visual matrix?

For fun I created a new table in this way:

New Matrix =
ADDCOLUMNS (
    DISTINCT ( 'DataSet'[Deal Number] ),
    "Sales Order Count", [Sales Order Count],
    "Deal Value Total", [Deal Value Total],
    "Deal Status", LOOKUPVALUE (
        'DataSet'[DEAL STATUS],
        'DataSet'[Deal Number], 'DataSet'[Deal Number]
    ),
    "Deal Posted Status", LOOKUPVALUE (
        'DataSet'[Posted Status],
        'DataSet'[Deal Number], 'DataSet'[Deal Number]
    )
)

Producing this matrix:

[Anonymous]

Greatly apprecaited sir!  This along with w/ a couple additional 'mod's solved it.  Thank you very much for the assistance. @ChrisMendoza 

[ChrisMendoza · Accepted solution]

@Anonymous -

These two Calculated Columns should help get you going:

DEAL STATUS = 
VAR countDeals =
    CALCULATE(
        COUNTROWS('DataSet'),
        ALLEXCEPT('DataSet','DataSet'[Deal Number])
    )

VAR countClosedDeals = 
    CALCULATE(
        COUNTROWS('DataSet'),
        ALLEXCEPT('DataSet','DataSet'[Deal Number],'DataSet'[Sales Order State])
    )
RETURN    
    IF(
        countDeals - countClosedDeals = 0,
        "COMPLETE",
        "ACTIVE"
    )

Posted Status = 
IF(
    'DataSet'[DEAL STATUS] = "COMPLETE",
    "Fully Posted",
    "Not Fully Posted"
)