cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hmblloyd Regular Visitor
Regular Visitor

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:

 

Line chart.pngThis is my table:

 

 Table.png

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!

 

11 REPLIES 11
BhaveshPatel Super Contributor
Super Contributor

Re: Cumulative total by week number

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. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Highlighted
hmblloyd Regular Visitor
Regular Visitor

Re: Cumulative total by week number

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 Smiley Happy

BhaveshPatel Super Contributor
Super Contributor

Re: Cumulative total by week number

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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
spuder Member
Member

Re: Cumulative total by week number

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. 

hmblloyd Regular Visitor
Regular Visitor

Re: Cumulative total by week number

It now let's me use the measure but it is an empty column so it isn't calculating?

 

My measure is:

 

Cumulative statuses = IF (
MIN ( 'Calendar'[Date] )
<= CALCULATE ( MAX ( OrgStatusHistory[Timestamp] ), ALL ( OrgStatusHistory ) ),
CALCULATE (
COUNTA ( OrgStatusHistory[Status Description] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)

 

 

spuder Member
Member

Re: Cumulative total by week number

CALCULATE (COUNTA(OrgStatusHistory[Status Description]),
FILTER (ALL (OrgStatusHistory),OrgStatusHistory[Timestamp]<= MAX('Calendar'[Date]))

Maybe this will help you. And keep in mind that you have to relate your DateTable

 

Greetings

v-yulgu-msft Super Contributor
Super Contributor

Re: Cumulative total by week number

Hi, @hmblloyd

 

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

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

Re: Cumulative total by week number

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.


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"


Power BI Blog
hmblloyd Regular Visitor
Regular Visitor

Re: Cumulative total by week number

Hi @v-yulgu-msft

 

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

 

Thanks,

Heather

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 56 members 1,077 guests
Please welcome our newest community members: