Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need Help Writing A Formula

Hello All,

 

I  would appreciate any ideas from the community to help me figue out how to write an appropriate formula for the scenario below.
I have data that has the following columns: incident number (Based on Calls to a department),date, and Names. I have been able to create a table with the number of calls per month by name( e,g Wifred ,Jan (20), February (50) etc.

What I want to create next is a formula that says: if Name(x) has not called three time in a 3 consecutive months then remove or do not show or edit the name from the list.  

Also, Is it possible to write a formula to show only the top 10 highest calls or I just use chart to shaow that.

Your ideas are welcome. Many thanks in advance.

Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Anonymous ,

    You can try  the measure below:

    remove not called =
VAR _TABLE =
    CALCULATETABLE (
        SUMMARIZE (
            'Data SUV',
            'Data SUV'[Name],
            'Data SUV'[Date].[Date],
            "_VALUE", [last three month]
        ),
        ALLEXCEPT ( 'Data SUV', 'Data SUV'[Name] )
    )
RETURN
    IF (
        COUNTAX ( FILTER ( _TABLE, [_VALUE] <> BLANK () ), [_VALUE] ) <> 0,
        COUNTROWS ( 'Data SUV' )
    )

    Results are as follows：

     

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-joesh-msft , Many thanks for offering the suggestiions of better ways to get help and laso your kind willingness to help.

       

      I have prepared a sample data to be attached but cannot find that option with this reply email. I will repost and hopefully you can see it.

       

      Thanks for your help so far.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-joesh-msft , I have shared a link from dropbox containing the sample file. I have removed names from the sample which is due to privacy concerns. The names help you to see the number of calls made by each name by the month.

         

        Let me know if you are able to access it.

         

        As mentioned in my previous post, my goal is to write a formula that (a) will delete or remove any name that has not called in the last three(3) consecutive months from the table and (b) a formula that show the top 10 highest callers at a glaance. I hope this explains my end goal better. Thanks

           https://www.dropbox.com/sh/qpnznp6elendawc/AADZNV67vx6Jo-oJ6f73FHYna?dl=0

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks to you all for helping me resolve the formual issue. I have tested it to see how it turns out with additional data.

     

    I have a calculated column for AGE based on the DOB and I wanted to show the ages in ranges such as 0-10, 11-20, 21-30 etc.

    Any ideas?? I have reviewed some possible answers from the forum but doesnt quite fit what I am looking for. I also used bins but it cpatures them into 10, 20, 30, 40 etc which is not the result I want to display in pie chart.

     

    Thanks