Forum Discussion

hpatel247's avatar
hpatel247
Icon for Helper I rankHelper I
8 years ago
Solved

running totals count

Hi,

 

I'm trying to show a cumulative total count of users per month but not sure how to do this. So far i have managed to get close to it however in my data, i can have multiple users in the same month but it counts each row. e.g in April i have 8 rows but 6 unique users, in May i have 4 users all unique you the total i would expect to show in May would be 10 (6 + 4) and so on each month

 

I'm using the below measure:

 

RunningTotal = var rowdata = FTE[Month] return CALCULATE(SUM(FTE[MTD]),FILTER(FTE,FTE[Month]<=rowdata && YEAR(FTE[Month])=YEAR(rowdata)))

 

Can someone help me with this as i have tried different methods from posts that are out there but they all are based on total sales or costs etc but i need one similar but as a cumulative count and not double counting if that makes sense.

 

regards

 

Hetal

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Try the Running Total quick measure. You could potentially replace the SUM with COUNT for the aggregator. If you can post sample data could provide a more complete answer.

  • p0nk's avatar
    p0nk
    Frequent Visitor
    8 years ago

    I belive you are looking for a measure like this:

     

    Running total of unique users per month = CALCULATE(DISTINCTCOUNT(TableName[UserColumn]), FILTER(ALL(TableName), TableName[Month] <= TableName[Month])))

     

    More information on the data you are using would be helpful.

     

    Hope this helps!

    • hpatel247's avatar
      hpatel247
      Icon for Helper I rankHelper I
      8 years ago

      Thanks for your response. I have replaced SUM with DISTINCTCOUNT and it works. However i now need to know how to count users more than once if they appear in 2 different categories.

       

      I have tried using COUNT and COUNTA but still doesn't count the user in each category. Is there a way round this

       

      regards

       

      Hetal

      • p0nk's avatar
        p0nk
        Frequent Visitor
        8 years ago

        I don't quite understand what you mean by different categorys? Are you able to post a sample of your data and an example of the result you want to achieve?