Forum Discussion

NewbieJono's avatar
NewbieJono
Post Partisan
2 years ago
Solved

Running Total

Hello all, i have a measure which creates a volume. how can I use dax to create a % based on total.

 

e.g

0-1 would be top row / total

1-2 would be , total of top two rows / total

2-3 would be, total of top 3 rows / total

 

here is the power bi table

 

I can do this in excel

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi NewbieJono ,

    I’d like to acknowledge the valuable input provided by sergej_og . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.  

    In my investigation, I took the following steps:

    I create a table as you mentioned.

    Then I create a new measure and a calculated column.

    Measure = 
CALCULATE (
    SUM ( 'Table'[Vol] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Days] <= MAX ( 'Table'[Days] ) )
)

    Column = SUM('Table'[Vol])

    Finally I create another measure and get what you want.

    Measure 2 = DIVIDE('Table'[Measure],MAX('Table'[Column]))

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • sergej_og's avatar
    sergej_og
    Super User
    2 years ago

    just an idea.
    Maybe this way:

    DIVIDE([your measure], CALCULATE([your measure], ALL() ))
    Adjust with your measures.
    ALL will ignore given filter context (0-1 days and so on) and calcualte your measure for the whole set.

    Regards
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi NewbieJono ,

    I’d like to acknowledge the valuable input provided by sergej_og . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.  

    In my investigation, I took the following steps:

    I create a table as you mentioned.

    Then I create a new measure and a calculated column.

    Measure = 
CALCULATE (
    SUM ( 'Table'[Vol] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Days] <= MAX ( 'Table'[Days] ) )
)

    Column = SUM('Table'[Vol])

    Finally I create another measure and get what you want.

    Measure 2 = DIVIDE('Table'[Measure],MAX('Table'[Column]))

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NewbieJono's avatar
      NewbieJono
      Post Partisan
      2 years ago

      Thanks for your help. howver this does not seem to work.  It may be because my vol column is a measure that returns a result

      • sergej_og's avatar
        sergej_og
        Super User
        2 years ago

        Can you provide this result (from your measure) as a calculated column in your model?
        Could make sense to do so.
        But you know your model best.

        Regards