0

I have a Power BI report that calculates the number of jobs due in a month and reports on how many jobs are completed on time. The report works well except the measure totals do not add up correctly like the table columns do.

I have measures in place for:

Done on time = Jobs completed in their due month 
Outside time = Jobs completed after when they were due.
Incomplete = Used to identify incomplete jobs
% Perf = Percentage of jobs completed on time."

Other fields: 

DUE = Due Date

completed = job completion date


I have found a possible answer to this using (HASONEFILTER) but cannot get it to work.

MEASURES

Done on time = 
    VAR DueMonth = MONTH ( FIRSTDATE ( Table1[due_date] ) )
RETURN
    CALCULATE ( 
        [Due],
        FILTER ( Table1,
        MONTH ( Table1[completed] ) = DueMonth || MONTH ( 
Table1[completed] ) = DueMonth -1 || MONTH ( Table1[completed] ) = DueMonth +1 
    )
)


Outside time = 
VAR DueMonth = MONTH ( FIRSTDATE ( Table1[due_date] ) )
RETURN
    CALCULATE ( 
        [Due] - [Done on time],
        FILTER ( 
            Table1,
            MONTH ( Table1[completed] ) <> DueMonth || MONTH ( Table1[completed] ) = DueMonth -1 || MONTH ( Table1[completed] ) = DueMonth +1 || 
        NOT ISBLANK ( Table1[completed] )
    )
)



% Perf = 
    DIVIDE ( 
        [Done on time],
        [Due],
        BLANK()
    ) * 100

Results would be properly totaled column measure's, screenshot below of existing output. Sample existing output

Garry McKay
  • 15
  • 5
  • The `SUMX(VALUES(...),...)` construction should work. Check out my answer here for more details: https://stackoverflow.com/questions/52953022 – Alexis Olson May 30 '19 at 15:09
  • Can you please point out better what goes wrong? – Aldert May 30 '19 at 15:24
  • For clarity if you total a measure i.e. Done on time, the total at the bottom of the image is incorrect – Garry McKay May 30 '19 at 15:29
  • Hi Alexis, I have looked at your post but am still unsure of how to construct the measure in this example, can you possibly apply this to one of the example measures like "Done on time" – Garry McKay May 30 '19 at 15:35
  • This is interesting, Done in time and Outside time both do not add up but together they do... I think it has to do with the fact you use measure, are you not better off using extra columns? – Aldert May 30 '19 at 17:39

0 Answers0