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

Cumulative Values By Month

Hey guys,

 

I need to make a line chart with monthly cumulative values for a measure I made. The measure tells me how many projects were finished on time (%).

The measure is below:

 

On time = DIVIDE (
CALCULATE ( COUNT ( baseLIC[On time?] ); baseLIC[On time?] = "Yes" );
CALCULATE ( COUNT ( baseLIC[On time?] ); ALLSELECTED(baseLIC[On time?]) )
)

 

Not sure the usual sumx or calculate suggestion works because it´s not really a sum.

I think the idea is to calculate for each month the cumulative total amount of projects with (Yes) divided by the cumulative total amount of projects (Yes+No).

 

Any suggestions?

 

Thanks! 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cumulative Values By Month

Hi @karimk,

 

You may download my file from here.

 

Untitled.png

View solution in original post

7 REPLIES 7
v-huizhn-msft Super Contributor
Super Contributor

Re: Cumulative Values By Month

Hi @karimk,

Based on my understanding, you create a line chart, the month is set on Axis, the y-axis shows how many projects were finished on time (%). If it is, please creat a table including month column, try to create a measure using the following formula and check if it works fine.

cumulative total11 =
VAR current =
    MAX ( 'Month'[Month] )
RETURN
    DIVIDE (
        CALCULATE (
            COUNT ( baseLIC[On time?] ),
            FILTER ( baseLIC, baseLIC[Month] <= mm && baseLIC[On time?] = "Yes" )
        ),
        CALCULATE (
            COUNT ( baseLIC[On time?] ),
            FILTER ( baseLIC, baseLIC[Month] <= mm )
        )
    )


For your measure posted, I don't know why do you use ALLSELECTED? There is a slicer in your report? We assume create a measure named("Onetime") to calculate how many projects were finished on time (%), we should get "Onetime" for Jan, "Onetime" of Jan+Feb on Feb month, "Onetime" of Jan+Feb+Mar on Mar month and so on.

In addition, you'd better share some sample data, you can create dummy data and expected result for better analyzing. So that we can reproduce your scenario.

Thanks,
Angelia

Super User
Super User

Re: Cumulative Values By Month

Hi,

 

Share some data and show the expected result.

karimk Regular Visitor
Regular Visitor

Re: Cumulative Values By Month

Hey guys. Thanks. I have a simple dataset in the link below.

 

https://www.dropbox.com/s/as25atr7ls4vq20/Sample%20Data.xlsm?dl=0

 

Cheers.

 

Super User
Super User

Re: Cumulative Values By Month

Hi @karimk,

 

You may download my file from here.

 

Untitled.png

View solution in original post

Highlighted
karimk Regular Visitor
Regular Visitor

Re: Cumulative Values By Month

Hi Ashish,

The link isn´t working.

Can you try again?

Super User
Super User

Re: Cumulative Values By Month

Hi,

 

The link is working just fine.  Please retry.

v-huizhn-msft Super Contributor
Super Contributor

Re: Cumulative Values By Month

Hi @karimk,

If the solution is acceptable, please mark the useful/helpful reply as answer, and welcome to share your own solution. More members will get workaround easily and clearly.

Thanks,
Angelia

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 216 members 2,196 guests
Please welcome our newest community members: