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

 

i want it something like

 

Cumulative Sum 

800

1600

.

.

.

.

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

7 Replies

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

         

  • 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

       

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  this should give you what you need

    _runningTotal =
CALCULATE (
    [Emp hours Week],
    FILTER (
        ALL ( 'Table'[WeekNum] ),
        'Table'[WeekNum] <= MAX ( 'Table'[WeekNum] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      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! 😄