Running Total for Charges

[edge9999]

I have a faily simple problem (I think) that i just cant get my head around.   We have numerous transaction lines, each with a dollar amount and a truck associated wtih them.    I'm trying to do a quick matrix where the Revenue per truck is summed up (easy part), and then the fees for that truck are calculated.    If the revenue is 25-150K the Fee is $300, if the Revenue is Greater than 150K, the fee is $500 (can also display this).   But the part i can't get is a total of the fees.   I need seom sort of running total on the column titled total in the matrix shown below.    I think i understand why the totals for the 25-150k and Over 150K columns dont work, but i just need to get some way to come up with the total fee and display it.   Any feedback is appreciated.

Formulas in columns 1, 2 and 3 repectively

MS_LINE_AMOUNT = sum(VW_TICKET_LINEITEMS[AMOUNT])

MS_25_150K = if([MS_LINE_AMOUNT] > 25000 && [MS_LINE_AMOUNT] <= 150000,300,0)

MS_OVER150K = if([MS_LINE_AMOUNT] > 150000 ,500,0)

[vivek31]

HI @edge9999 ,

you can try this formula to find running total for charges

running total = 
var current_unit = MAX(VW_TICKET_LINEITEMS[Unit])
RETURN
CALCULATE(SUMX(FILTER(ALL(VW_TICKET_LINEITEMS),
                      VW_TICKET_LINEITEMS[Unit] <= current_unit),[Total Charges]))

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

[Anonymous]

Hi, @edge9999 

You can try the following methods.

Measure 25_150K = SUMX(VW_TICKET_LINEITEMS,[MS_25_150K])

Measure OVER150K = SUMX(VW_TICKET_LINEITEMS,[MS_OVER150K])

Measure Total = [Measure 25_150K]+[Measure OVER150K]

Total = SUMX(FILTER(ALL(VW_TICKET_LINEITEMS),[Unit]<=MAX(VW_TICKET_LINEITEMS[Unit])),[Measure Total])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

[lbendlin]

Consider using a Quick Measure for that. It has a "Running Total"  pattern that is pretty solid.

[edge9999]

I think i may have missed explaining a piece.    There are multiple transactions for each Unit which means that in the visual above, the Revenue is actually the sum of many records.    I dont think i can use SUMX as the Revenue needs to be summed for the period before I can determine what rate to apply (either the $300 or the $500).    I also tried adding a quick measure for a running total with no luck.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[edge9999]

I have attached some sample data.  The first tab is raw data.  The Calc tab is what i'm trying to create in Power BI

Link 

Thanks