Running Total

[NewbieJono]

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

[v-yilong-msft · Accepted solution]

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]

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]

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

[NewbieJono]

My volume measure  is:

Volume Measure = 
CALCULATE (
    SUM ( 'FACT- Output'[Total] )
        + CALCULATE (
            SUM ( 'FACT - Process Volumes'[Volume] ),
            'FACT - Process Volumes'[NEW Days Range] = "0-1 Days"
        )
)

with your code i have managed to get the running total. i just now need to divide each row by the total

[sergej_og]

Pls try to get a measure which is ignoring provided filter context. In your screenshot I can identify your given categories (0-1 Days, 1-2 Days etc.).
Try to create a measure with ALL()...copy your "Volume Measure" and adjust with ALL(your Table data comes from).
So that you will get 1,257,649 in every row you have in your visual.
Play a bit around with that. You know your model best.

After you have your measure with a Total in every row you can just divide one by other.

Regards

[NewbieJono]

Thank you, i managed to get it going with a bit of messing (like you said!) The measures have resulted some of the days with no data being shown. Is there a method to stop this in the dax or do I just filter out the blanks.

The measure that is triggering all empty rows to show is

Measure = 
CALCULATE (
    [Volume Measure],
    FILTER ( ALL ( 'Table' ), 'Table'[Keying To Output Day] <= MAX ( 'Table'[Keying To Output Day] ) )
)

New output but i don't want to show vol that are blank

[sergej_og]

Maybe you can try with a IF-Statement.
Like this...
IF( [Volume measure] <> BLANK(),
     [Tes6])

[NewbieJono]

Thanks Everyone for your help with this

[sergej_og]

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