Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
3 years ago
Solved

Count Visited Frequency

Hi BI Community Team,   I have a table of visited summary report that sales rep went to visit customer but one customer was service with multiple sales rep.   And we want to know the visited freq...
Need Help
ADSL's avatar
ADSL
Post Prodigy
3 years ago

Hi ThxAlot,

Thank you for your helpful feedback, but after trying the measure that you advise in my sample file.

 

It cannot load the data as the screenshot below. If trying your file sharing, it's working fine.

 

Any suggestion?

 

ThxAlot's avatar
ThxAlot
Super User
3 years ago

It works on my side ... anyway, here is the key measure used in the viz

 

Cnt = 
SUMX(
    VALUES( Frequency[Frequency] ),
    VAR __cnt = CALCULATE( MAX( Frequency[Index] ) )
    RETURN
        SUMX(
            VALUES( VISITED_SUMM[SALESREP_CODE] ),
            COUNTROWS(
                FILTER(
                    VALUES( VISITED_SUMM[CUST_CODE] ),
                    VAR __dc =
                        CALCULATE( DISTINCTCOUNT( VISITED_SUMM[VISIT_DATE] ) )
                    RETURN
                        __dc = __cnt || __cnt > 8 && __dc > 8
                )
            )
        )
)

 

Drag and drop colmn/row into a Matrix viz, that's all.

 

    • ThxAlot's avatar
      ThxAlot
      Super User
      3 years ago

      You need to change the data type of Frency[Index] to whole number.

      • ADSL's avatar
        ADSL
        Post Prodigy
        3 years ago

        Hi ThxAlot,

        Thank you very much for your helpful feedback and support.

        Best Regards,