Forum Discussion

Raketenrudi's avatar
Raketenrudi
Regular Visitor
1 year ago
Solved

Table with 'blanks' and calculating 'Coverage' based on customers, calls and time

Hi,    There are two tables I'd like to work with and a date slicer.    Table 'Customer' Customer ID Customer Infos ... A [Not relevant] B [Not relevant] C [Not relevant] D ...
  • Elena_Kalina's avatar
    1 year ago

    Hi Raketenrudi 

    Try these measures

    Last Call Date = 
    VAR CustomerID = SELECTEDVALUE(Customer[Customer ID])
    VAR MaxMonthNumber = 
        CALCULATE(
            MAX(Calls[MonthNumber]),
            FILTER(
                ALLSELECTED(Calls),
                Calls[Customer ID] = CustomerID
            )
        )
    VAR LastMonthName = 
        SWITCH(
            MaxMonthNumber,
            1, "JAN", 2, "FEB", 3, "MAR", 4, "APR",
            5, "MAY", 6, "JUN", 7, "JUL", 8, "AUG",
            9, "SEP", 10, "OCT", 11, "NOV", 12, "DEC",
            "нет"
        )
    RETURN
        IF(ISBLANK(MaxMonthNumber) || MaxMonthNumber = 0, "none", LastMonthName)
    Call Count = 
    VAR CustomerID = SELECTEDVALUE(Customer[Customer ID])
    VAR Result = 
        CALCULATE(
            DISTINCTCOUNT(Calls[Call ID]),
            FILTER(
                ALLSELECTED(Calls),
                Calls[Customer ID] = CustomerID
            )
        )
    RETURN
        IF(ISBLANK(Result), 0, Result)

     

    Coverage = 
    VAR CustomersWithCalls = 
        CALCULATE(
            DISTINCTCOUNT(Customer[Customer ID]),
            FILTER(
                Customer,
                [Call Count] > 0
            )
        )
    VAR TotalCustomers = 
        DISTINCTCOUNT(Customer[Customer ID])
    RETURN
        DIVIDE(CustomersWithCalls, TotalCustomers, 0)

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

    Thank you.