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 frequency per month like - 1 Time | 2 Time | 3 Time | .... | 8 Time | > 8

 

Any suggestion/advise?

 

Thanks and Regards,

Need Help

6 Replies

    • 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.