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
Anonymous
Not applicable

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:

Screen Shot 2019-07-30 at 6.15.44 PM.png

 

Desired Column/Table Ouptut 

Screen Shot 2019-07-30 at 6.16.14 PM.png

 

New Matrix for Operations Team to Manage Deals

Screen Shot 2019-07-30 at 6.17.07 PM.png

 

 

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

1 ACCEPTED SOLUTION
ChrisMendoza
Resident Rockstar
Resident Rockstar

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

2.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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:

3.PNG

 

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

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

ChrisMendoza
Resident Rockstar
Resident Rockstar

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

2.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.