Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Cumulative total by week number

I want to produce a line chart that shows the cumulative count of statuses by week number. I can get it to show the count per week, but I am struggling to change this to show the cumulative amount. This is my line chart at the moment:

 

This is my table:

 

 

Can anyone help me to get the line chart to show the running total instead of the count per week? (I am new to Power BI!!)

 

Thanks!

 

Need Help

11 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    9 years ago
    Cumulative Statuses :=
    CALCULATE (
     COUNT( Table1[Status Description] );
     FILTER ( ALL ( Table1 ); Table1[Time Stamp] <= MAX ( Table1[Time Stamp] ) );
     VALUES ( Table1[Week number] )
    )

    Try this measure and It would solve your problem. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thank you. I didn't have a calendar table in my model. I have created one and linked it to my status history table on the timestamp column.

       

      I have created the measure however when I try to use it in a visualisation it gives me the error message: Calculation error in measure 'OrgStatusHistory'[Cumulative statuses]: The function COUNT takes an argument that avaluates to numbers or dates and cannot work with values of type string.

       

      Any ideas how to fix that?

       

      Thanks for your help :)

      • BhaveshPatel's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        9 years ago

        Sorry my mistake. Use COUNTA instead as your data type for the status column is string.

    • spuder's avatar
      spuder
      Icon for Resolver IV rankResolver IV
      9 years ago

      Hi Bhavesh,

       

      I tried to create some sample data and tried your measure. Unfortunetaly I didn't get the right result. But when I erased the second filter of the CALCULATE() function it worked. 

      I read that the VALUES() function bring back the filter. So in my understanding he counts only the values of the weeknum. And that was what happened. Am I right with my thoughts or is there something I can't see. 

       

      Thanks in advance. 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi, Anonymous

     

    In your scenario, use weeknumber as Axis, use statusDescription as Legend and use the below measure as Value

    Cumulative Statuses = 
CALCULATE (
SUM( 'Cumulative total'[Coumt]),
FILTER ( ALL( 'Cumulative total' ),'Cumulative total'[weeknumber] <= MAX ( 'Cumulative total'[weeknumber] ) )
)

    Best regards,
    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi v-yulgu-msft

       

      I don't have a cumulative total table - do I need to create this?

       

      Thanks,

      Heather

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi, Anonymous

         

        In my test, I named the table you provided in your original post as cumulative total table. So, you don't need to create this, just replace the table name with yours in the formula.

         

        Thanks,
        Yuliana Gu

         

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi there you should be able to use the following DAX syntax below.

     

    Cumulative Part Sales =

     

    Cumulative Part Sales = 
CALCULATE (
    SUM ( 'Parts Sales Line'[Line Amount] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Parts Sales Line'[Sales Order Created Date] )
    )
)

     As long as you have got your relationship between your date table and your fact table, you MUST then drag in the column from the Date Table (Week) and it should display correctly.