Calculation of monthly progress percentage

[Anonymous]

My data looks something like this, Planned is coming from one table and Actual from other which i have connected over date table.

I'm trying to ploting % values using dates as axis Or by months Bins as axis on Clustered column chart, 

Like here for example , In Sept Total Planned = 57 , Total Actual till date in Sept = 59  .

I am having difficulty preparing measure for Below

So Total effort in Sept = (59/57)*100 = 103%

For the month of Sept actual % = 47% & Sept Plan % =57/124 = 46%.

Total Effort in Oct = (0/67)*100 = 0%

Similarly, I'll be doing for many months and acutals will keep coming every month on clustered chart

Also for Running total, I just used the running total Quick measure for Planned , But for Actual, since it is dividing by itself by End of Sept running total is showing 100% where as it should be 47.58%

[danextian]

If you don't have Dates table yet, create one. You may use the formula below to create a calculated calendar table:

Calendar =
ADDCOLUMNS (
    CALENDAR (
        MIN ( MIN ( PlanTable[Plan Dates] ), MIN ( ActualTable[Actual Dates] ) ),
        MAX ( MAX ( PlanTable[Plan Dates] ), MAX ( ActualTable[Actual Dates] ) )
    ),
    "Month Name", FORMAT ( [Date], "mmm" )
)

Create a single direction relationship between the Date column in Calendar table & PlanTable[Plan Dates] and Date column in Calendar table & ActualTable[Actual Dates].  Create this measure:

Percentage =
DIVIDE ( SUM ( ActualTable[Actual Work] ), SUM ( PlanTable[Planned Work] ) )

Add the MonthName column from Calendar to a visual and then the Percentage measure.

[Anonymous]

Hey @danextian  this Really helped me calculate Effort % work done per month. But I'm also trying to Plot this type of graph.

Here all are in numbers, I'm Trying to get this chart in Percentage. Orange is planned and Blue is actual.

Can you help me please? 

[dax]

Hi Zahid_shaikh22,

It seems that you want to show precentage and acutal value in the same chart, right? If I have something misunderstood, please correct me. If so , you could try to use Line and Clustered column chart and use secondary axis for chart like below

Best Regards,
Zoe Zhi

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

[Anonymous]

@dax  No, The graph photo that I shared, they are in numbers. I want to show them in form of percentage. Planned % and Actual %

[dax]

Hi Zahid_shaikh22,

If you want to show value as percentage, you could choose corresponding field, then click modeling->% to change format. If you want to show % based on each month, I think you need to create measure to achieve this goal( you might need to filter in measure, filter (table, month=min(calendar[month])). Or you could upload your report file on forum, please do mask sensitive data before uploading.

Best Regards,
Zoe Zhi

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