Forum Discussion

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
1 year ago
Solved

Struggling with getting a total from component column

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...
Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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?

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
1 year ago

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's avatar
    masplin
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    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's avatar
      masplin
      Icon for Impactful Individual rankImpactful Individual
      1 year ago

      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 £])))
  • masplin's avatar
    masplin
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    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