Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

power bi

Hi, I have a question,    As you can see I want to calculate the Cumulative sum of  Emp hours Week Except Week 0. and Emp hours week is a measure ( not a coloumn )   How do I achieve that? Can ...
General Comment
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI ALL, 

     

    the solution is : 

     

    Avrg Emp Hours =

    VAR WEEKNUMBER =
    MAX ( 'Yearly Data'[WeekNum] )
    VAR temp =
    SUMMARIZE (
    ALL ( 'Yearly Data' ),
    'Yearly Data'[WeekNum],
    "CumulativeTotal", [Emp Hours Week]
    )
    VAR Result =
    FILTER ( temp, 'Yearly Data'[WeekNum] <= WEEKNUMBER && 'Yearly Data'[WeekNum] > 0 )
    RETURN
    SUMX ( Result, [Emp Hours Week] )
     
    Thanks to MFelix  for the solution! 😄
speedramps's avatar
speedramps
Super User
4 years ago

Try this    Click here to download PBIX 

 

How is works ...

 

Cummulative =
VAR myweek = SELECTEDVALUE(Facts[Week])
RETURN
CALCULATE(
SUM(Facts[Emp Hours]),
ALL(Facts[Week]),
Facts[Week] <= myweek && Facts[Week] <> 0
)
 
 
 
myweek gets the current week with SLECETDVALUE,.
CALCULATE allows you to overrides the default weeky by week filter.
SUM aggregates the hours.
ALL overrides the default weeky by week filter.
Facts[Week] <= myweek aggregates upto and inlcusing the current week.
Facts[Week] <> myweek ignores week zero as requested,.