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,.
 
 

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

hi,

 

Cummulative =
VAR myweek = SELECTEDVALUE(Facts[Week])
RETURN
CALCULATE(
SUM(Facts[Emp Hours]),
ALL(Facts[Week]),
Facts[Week] <= myweek && Facts[Week] <> 0
)
 
The ISSUE for me is Emp Hours is A measure and not column. 
So SUM function takes only column as input.
how do i use emp hours week ( measure)?
  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    In answer to your question "how do I use emp hours as a measure?"

     

    It depend on the complexity of the measure.

    It is somethign simple like 

    emphours = SUM(Facts[Emp Hours])

     

    then this will produce excatly the same answer as before

    see   Click here to download PBIX 

     

     

    Cummulative2 =
    VAR myweek = SELECTEDVALUE(Facts[Week])
    RETURN
    CALCULATE(
    [emphours]
    ALL(Facts[Week]),
    Facts[Week] <= myweek && Facts[Week] <> 0
    )

     

     

    how it works ....

    a measure is not a stoire varible. it has to be recalculate in contect each time it is referenced.

    So if you imbed a measure within CALCULATE and ALL context then it will alter the contect accordingly, unless your first measure has conflicting commands which the second cant override,