Struggling with getting a total from component column

[masplin]

Hi.

i have a model with sales opportunties table and another table which records the date at which an opportuntiy moves past a stage in the process (qualify, devlop, propose, quote,close). Sometimes opp are won before they get to the end of the process.

I have created ameasure to calculate in a given month what business was one from opp in each state

SalesStagesort is a disconnected table so that i can put those stages on the columns witohut filtering any data.  Sales process is the table that lsits al lthe stages and the dates they were completed.  Stage rank puts the steps in reverse date order so I can find the last one within the date window.  Opp[Close Date] filters for opp won during date window. Opportunity ID is the unique identifier of the opp on both opp and Sales stage tables. 

Won Value £ = 
VAR
Maxdate=MAX(DateTable[Date])
VAR
Mindate=MIN(DateTable[Date])
VAR
Stage=SELECTEDVALUE(SalesStageSort[Sales Stage])
RETURN

    SUMX(FILTER(ADDCOLUMNS(Opportunity,
                "SalesStage",IFERROR(SUMMARIZE(TOPN(1,
                                           FILTER(RELATEDTABLE('Sales Process'),'Sales Process'[Completed Date]<=Maxdate),
                                           'Sales Process'[Stage Rank],
                                           ASC),'Sales Process'[Sales Stage At]),"Error")),
            [SalesStage]=Stage  &&
            Opportunity[Close Date]<=Maxdate && Opportunity[Close Date]>=Mindate),
            [Act Value Won £])

all works great except no row total or total total

I tried putting another SUMX around my measure, but seemed to create gibberish. I tried SUMX(opportunity,  SUMX(Sales Stage etc etc. KJust cant work out how to add up the columns into a total!!!!

I can see in the total column there is no sales stage to pass to the measure which is why it is blank. 

Any suggestions appreciated

Mike

[OwenAuger · Accepted solution]

Hi @masplin 

A quick fix would be to create another measure Won Value £ summed that iterates over SalesStageSort, summing Won Value £ for each row, and use that in your visuals:

Won Value £ summed = 
SUMX (
    SalesStageSort,
    [Won Value £]
)

or you could include the code from the original measure wrapped in CALCULATE (or some variation of this):

Won Value £ summed = 
SUMX (
    SalesStageSort,
    CALCULATE (
        // Original Measure Expression here
    )
)

Alternatively, you could create a "dynamic segmentation"-style measure similar to the last measure shown in DAX Patterns: Dynamic Segmentation.

Here's how I would write it given my understanding of your model:

Won Value £ (Dynamic Segmentation) = 
VAR Maxdate = MAX ( DateTable[Date] )
VAR DateRange = VALUES ( DateTable[Date] )
VAR OpportunityLatestStage =
    CALCULATETABLE (
        INDEX (
            1,
            SUMMARIZE ( 'Sales Process', 'Sales Process'[Stage Rank], 'Sales Process'[Sales Stage At], Opportunity[Opportunity ID] ),
            ORDERBY ( 'Sales Process'[Stage Rank], ASC ),
            DEFAULT,
            PARTITIONBY ( Opportunity[Opportunity ID] )
        ),
        KEEPFILTERS ( TREATAS ( DateRange , Opportunity[Close Date] ) ), -- Close Date filter
        KEEPFILTERS ( 'Sales Process'[Completed Date] <= Maxdate ), -- Completed Date filter
        ALLSELECTED ( ) -- Optimization to reuse cached results. OpportunityLatestStage table should be computed once.
    )
VAR OpportunitiesInStage =
    FILTER (
        OpportunityLatestStage,
        VAR StageForOpportunity =
            FILTER ( SalesStageSort, SalesStageSort[Sales Stage] = 'Sales Process'[Sales Stage At] )
        VAR IsOpportunityInStage = NOT ISEMPTY ( StageForOpportunity )
        RETURN
            IsOpportunityInStage
    )
VAR Result =
    CALCULATE (
        SUM ( Opportunity[Act Value Won £] ),
        KEEPFILTERS ( OpportunitiesInStage )
    )
RETURN
    Result

Do any of these work for you?

[masplin]

So I think I understand your dynamic version. 

First get a table for every opportunity of what its last stage was up to the max date.  The index function creates groups of partition of each oppID, sorts them by rank and pciks of the last one.  The TREATAs means the close date is in the selcted date range.  Not sure what the ALLSELECTED bit means?

The 2nd bit makes a table of al lthe oppID from the firest stage where the Stage At matches the stage in the visual . So basically a table of Opp ID, sales stage At

Lastly calcuate the expression for all these Opp ID

So does my version iterate over and over and your version do some calculations just once?  Good education for me

Thanks a lot

[masplin]

Hi Owen

Yes the top one works.  The second version I just put SUMX(SalesStagesort,
                                                                                                SUMX(expression......
So didnt wrap the expression in a CALCULATE. Why do you need ot do that?

Your last version does this have performance benefits over my amatuer code? I dont really understna dit, but if its going to be superior I'll work on getting head round it.

Thanks very much for the reply

[masplin]

Hi

Actually 2nd version doesnt work if i 've done this right?

Won £ by Stage = 
VAR
Maxdate=MAX(DateTable[Date])
VAR
Mindate=MIN(DateTable[Date])
VAR
Stage=SELECTEDVALUE(SalesStageSort[Sales Stage])
RETURN
SUMX(SalesStageSort,
CALCULATE(
    SUMX(FILTER(ADDCOLUMNS(Opportunity,
                "SalesStage",IFERROR(SUMMARIZE(TOPN(1,
                                           FILTER(RELATEDTABLE('Sales Process'),'Sales Process'[Completed Date]<=Maxdate),
                                           'Sales Process'[Stage Rank],
                                           ASC),'Sales Process'[Sales Stage At]),"Error")),
            [SalesStage]=Stage  &&
            Opportunity[Status]="Won" &&
            Opportunity[Close Date]<=Maxdate && Opportunity[Close Date]>=Mindate),
            [Act Value Won £])))