CODEWITHSUNDEEP
powerbidaxpowerbi-desktopburndowncharts
Matt
Matt

Reputation: 51

SUMMARIZE or SUM values based on Date fields in another table

I am trying to do a burndown graph on PowerBI.

My data is a list of tasks. Each task has a numerical value (EFFORT) assigned to it and there is a total amount of effort for any given month (sum of all EFFORT). As tasks as set to DONE, the ongoing effort should be deducted from a running total and that value used to plot a graph. I have 3 columns

enter image description here

I would like to have measure to calculate EFFORT REMAINING for each date, i.e.

EFFORT REMAINING = TOTAL EFFORT - (EFFORT WHEN TASKS ARE DONE FOR EACH DAY)

For example,

enter image description here

I did get the consecutive dates displaying:

Burndown = CALENDAR(DATE(2022,7,1),DATE(2022,7,31))

enter image description here

and also the total effort (starting value)

TOTAL EFFORT = SUM(Issues[EFFORT])

Now for each date in table, I need to minus the accumulating total of EFFORT when the status is set to DONE

EFFORT REMAINING = Burndown[TOTAL EFFORT]-SUM(Issues[EFFORT]="DONE" ....

Im stuck after this last point. Can anyone help, please?

Upvotes: 0

Views: 2992

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

Please try this measure:

Please ensure that (1-Many) relationship is created between Burndown [Date] and Issues[ISSUE_CREATED] columns.

EFFORT REMAINING =
VAR TblSummary =
    ADDCOLUMNS (
        SUMMARIZE ( Issues, Burndown[Date] ),
        "Total Effort", CALCULATE ( SUM ( Issues[EFFORT] ) ),
        "Tasks Completed", CALCULATE ( SUM ( Issues[EFFORT] ), Issues[STATUS] = "DONE" ),
        "Effort Remaining",
            CALCULATE ( SUM ( Issues[EFFORT] ) )
                - CALCULATE ( SUM ( Issues[EFFORT] ), Issues[STATUS] = "DONE" )
    )
VAR Result =
    SUMX ( TblSummary, [Effort Remaining] )
RETURN
    Result

After that, you can create a clustered column chart, and put [Date field] on calendar table on X_axis and put 'EFFORT REMAINING' measure on Y_axis(Value axis) to see the result.

I hope It solves your problem.

Bonus Info: If you want to see your Summary table, create a "New Table" and paste this code:

Summary_Table =
VAR TblSummary =
    ADDCOLUMNS (
        SUMMARIZE ( Issues, Burndown[Date] ),
        "Total Effort", CALCULATE ( SUM ( Issues[EFFORT] ) ),
        "Tasks Completed", CALCULATE ( SUM ( Issues[EFFORT] ), Issues[STATUS] = "DONE" ),
        "Effort Remaining",
            CALCULATE ( SUM ( Issues[EFFORT] ) )
                - CALCULATE ( SUM ( Issues[EFFORT] ), Issues[STATUS] = "DONE" )
    )
VAR Result =
    SUMX ( TblSummary, [Effort Remaining] )
RETURN
    TblSummary

The result It produces: Note: I have limited access to your data sets as you shared above. The result will be exact with your full dataset.

KJK

Upvotes: 2

Mik
Mik

Reputation: 2103

you are so close to the answer ). Convert SUM(Issues[EFFORT]="DONE" to:

CALCULATE(
    SUM(Issues[EFFORT])
    , SUM(Issues[Status]="DONE"
)

Have a nice day.

Upvotes: 2

Related Questions