Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculated Columns that relate to like row grouping.

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

Tutorial Requests
  • ChrisMendoza's avatar
    ChrisMendoza
    7 years ago

    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"
)

3 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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"
)

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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