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! 😄
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,