power bi

[Anonymous]

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 you help with this?

i want it something like

Cumulative Sum 

800

1600

.

.

.

.

[smpa01]

@Anonymous  this should give you what you need

_runningTotal =
CALCULATE (
    [Emp hours Week],
    FILTER (
        ALL ( 'Table'[WeekNum] ),
        'Table'[WeekNum] <= MAX ( 'Table'[WeekNum] )
    )
)

[Anonymous · Accepted solution]

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]

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]

hi,

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

[speedramps]

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]

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]

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,