Count a Measure that uses YTD

[Anonymous]

Hi All!

Hope you guys can help me with this one.

I'm trying to figure out how many customers were won or lost on a specific month and then aggregate on higher levels.

The image above shows the 2 tables I'm using:

• d_Calendar: date dimension (joined by [Date] and [Calendar Month]
• f_Customer Profitability: fact table with sales by customer and by date. Each customer has some regions and other aggregations, but [Name and Number] are the lowest level.

In order to evaluate if the customer was lost or won, I'm using the company's rule and created the following measures:

# Net Sales Actual Month

Net Sales: Actual = SUM('f_Customer Profitability'[Net Sales])

# Year-to-Date Net Sales (Sum from January till Actual Month)

Net Sales: Actual (YTD) = TOTALYTD( SUM('f_Customer Profitability'[Net Sales]), d_Calendar[Date], ALL('d_Calendar') )

# Average to Date Net Sales (Average from January till Actual Month)

Net Sales: Actual (ATD) = TOTALYTD( AVERAGE('f_Customer Profitability'[Net Sales]), d_Calendar[Date], ALL('d_Calendar') )

# Last Year's Year-to-Date Net Sales (Sum from January till Actual Month, but Last Year)

Net Sales: Previous Year (YTD) = CALCULATE([Net Sales: Actual (YTD)], SAMEPERIODLASTYEAR(d_Calendar[Date]))

Next, with these measures (which are working fine and I've confirmed the results with an Excel file), I calculate whether the customer is a Win or a Loss with the following measure:

Win Or Loss =

// Loss

IF([Net Sales: Previous Year (YTD)] > 5000 && [Net Sales: Actual] < 1, "Loss",

// Win

IF([Net Sales: Previous Year (YTD)] < 1000 && [Net Sales: Actual (ATD)] > 1000, "Win",

// Else

BLANK()

))

The Win/Loss result must be calculated on the Customer level ([Name and Number]), which is the lowest level in the table.  This measure is working as well and I'm getting the following results in a Matrix:

I believe the problem starts here:

Then I want to count them and aggregate them on a higher level, for instance [Region Code], and see which Region has higher Win/Loss.

The problem is, I want to count how many wins and how many losses in a specific month I had, by [Region Code]. I tried using the following Measure, but it is not working. Basically, I want to count the results of a measure.

Count Wins = COUNTAX('f_Customer Profitability', [Win Or Loss] = "Win")

I think this is not working because the YTD and ATD won't work on a row by row count.

Could you please help? 🙂

[daxer-almighty]

"What I'm trying to accomplish now is to SUM the [Net Sales] and measure how much I won, but only for the customers that are still a "win" in the month I selected on the Slicer, if I lost them again during the year, I should not consider their [Net Sales]."

// With a proper model (where Dates is a date dimension table),
// you would write:

[Total Net Sales] = SUM( FactTable[Net Sales] )

[Total Net Sales (Wins+)] =
var WinCustomersInCurrentPeriod =
    FILTER(
        DISTINCT( Customer[CustomerID] ),
        [Win or Loss] = "Win"
    )
var PeriodAfterCurrentPeriodWithinSameYear =
    var LastVisibleDate = MAX( Dates[Date] )
    // Dates[Year] must be an int. If it's not,
    // then use a column that is an int and
    // encodes years.
    var LastVisibleYear = MAX( Dates[Year] )
    return
        CALCULATETABLE(
            DISTINCT( Dates[Date] ),
            LastVisibleDate < Dates[Date],
            Dates[Date] <= DATE(LastVisibleYear, 12, 31),
            // REMOVEFILTERS is not necessary if
            // Dates is marked as a date table
            // in the model. Otherwise, you should
            // use it.
            REMOVEFILTERS( Dates )
        )
var CustomersNotLostAfterCurrentPeriodWithinSameYear =
    FILTER(
        DISTINCT( Customer[CustomerID] ),
        CALCULATE(
            [Win or Loss] <> "Loss",
            PeriodAfterCurrentPeriodWithinSameYear,
            REMOVEFILTERS( Dates )
        )
    )
var CustomersInIntersection =
    INTERSECT(
        WinCustomersInCurrentPeriod,
        CustomersNotLostAfterCurrentPeriodWithinSameYear
    )
var TotalNetSalesForIntersection =
    CALCULATE(
        [Total Net Sales],
        CustomersInIntersection
    )
return
    TotalNetSalesForIntersection

[daxer-almighty]

By the way, your YTD measures are not quite correct 🙂 They will, as much as I can tell, return figures even for periods in the calendar that are in the future. Also, comparing YTD to prior YTD will be incorrect for periods that cover TODAY. If a period contains TODAY, the context must be adjusted so that like for like periods are compared.

[daxer-almighty · Accepted solution]

[Win/Loss Ratio] =
var Wins =
    COUNTROWS(
        FILTER(
            // CustomerID must uniquely identify
            // a customer.
            DISTINCT( Customer[CustomerID] ),
            [Win or Loss] = "Win"
        )
    )
var Losses = 
    COUNTROWS(
        FILTER(
            // CustomerID must uniquely identify
            // a customer.
            DISTINCT( Customer[CustomerID] ),
            [Win or Loss] = "Loss"
        )
    )
var Result =
    DIVIDE( Wins, Losses )
return
    Result

By the way, Customer must be its own dimension. If you keep a design as the one above, don't be surprised when the figures will randomly get distorted and you won't be able to find out where they come from. One-table models like the one above have a potential (very real) to give you wrong numbers even for good formulas. Beware of one-table models!

If you want to see what I'm talking about, you can watch this: (1) Auto-exist on clusters or numbers - Unplugged #22 - YouTube

Here's another way (might be faster) to write your measure:

[Win/Loss Ratio] =
var WinLossTable =
    FILTER(
        ADDCOLUMNS(
            DISTINCT( Customer[CustomerID] ),
            "@WinOrLoss",
                [Win or Loss]
        ),
        NOT ISBLANK( [@WinOrLoss] )
    )
var Wins =
    COUNTROWS(
        FILTER(
            WinLossTable,
             [@WinOrLoss] = "Win"
        )
    )
var Losses = 
    COUNTROWS(
        FILTER(
            WinLossTable,
             [@WinOrLoss] = "Loss"
        )
    )
var Result =
    DIVIDE( Wins, Losses )
return
    Result

And still another (could be even faster than the one above):

[Win/Loss Ratio] =
var WinLossTable =
    FILTER(
        ADDCOLUMNS(
            DISTINCT( Customer[CustomerID] ),
            "@WinOrLoss",
                [Win or Loss]
        ),
        NOT ISBLANK( [@WinOrLoss] )
    )
var Wins =
    COUNTROWS(
        FILTER(
            WinLossTable,
             [@WinOrLoss] = "Win"
        )
    )
var TotalCusts =
    COUNTROWS( WinLossTable )
var Result =
    DIVIDE( Wins, TotalCusts - Wins )
return
    Result

[Anonymous]

Thank you @daxer-almighty ! You are the best! You saved my life! 🙂

I was wondering if you could give just one more help 🙂

I worked on the tips you gave me, and now I have the FACT table separated (as you can see below). Those tables come straight from the company's Analysis Services.

With your help, I was able to count how many wins I have for each month, I'm using the first formula you gave me, using the "Performance Analyzer" seems to be the fastest one. What I'm trying to accomplish now is to SUM the [Net Sales] and measure how much I won, but only for the customers that are still a "win" in the month I selected on the Slicer, if I lost them again during the year, I should not consider their [Net Sales].

I have a Slicer, as shown above, the users can select the "Year" and "Month" they want to analyze. The issue is, I want to SUM only when there is a Win on the month I selected. So, if I won a client in January and lost him in May, it should not count and SUM the [Net Sales] for that Customer. On the other hand, if I won a client in April and it is still a win in June (The month I selected), I want to SUM its revenue.

Wins $ =

var c_Month = MAX('d_Calendar'[Month #])

var c_Year = MAX(d_Calendar[Year])

var DateSelected = DATE(c_Year, c_Month, 1)

var DateStart = DATE(c_Year, 1, 1) // Starts in January

var Wins =

CALCULATE(

SUM('f_Customer Profitability'[Net Sales]),

FILTER(

// CustomerID must uniquely identify a customer.

DISTINCT( 'd_Customer'[Customer CRC.Customer CRC Number] ),

[Win or Loss] = "Win"

)

)

return

Wins

I want to SUM the [Net Sales] only when [Wins] = 1 (Using the formulas you gave me) and [Date] = DateSelected (From Slicer). This formula is SUMMING the Net Revenues even if I've already lost the customer. I basically want to sum if [Wins] = 1 on the month selected.