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! 😄
arvindsingh802's avatar
arvindsingh802
Community Champion
4 years ago

The solution 

EMP_Hour = CALCULATE(SUM('Table'[Hour]), FILTER('Table','Table'[Week] <>0))
 
EMP_Hour running total in Week =
CALCULATE(
    'Table'[EMP_Hour],
    FILTER(
        ALLSELECTED('Table'[Week]),
        ISONORAFTER('Table'[Week], MAX('Table'[Week]), DESC)
    )
)
 
If you need to show week 0 in your visual then enable Show Item with no data for week column

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

hi,

 

I used your formula and got this,still the same values