topic Re: Running Total for Charges in DAX Commands and Tips

Running Total for Charges

edge9999 — Tue, 07 Jan 2025 20:51:07 GMT

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)

Re: Running Total for Charges

lbendlin — Tue, 07 Jan 2025 23:39:16 GMT

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

Re: Running Total for Charges

Anonymous — Wed, 08 Jan 2025 05:44:20 GMT

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.

Re: Running Total for Charges

vivek31 — Wed, 08 Jan 2025 05:29:18 GMT

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.

Re: Running Total for Charges

edge9999 — Wed, 08 Jan 2025 18:46:34 GMT

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.

Re: Running Total for Charges

lbendlin — Wed, 08 Jan 2025 21:19:32 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Running Total for Charges

edge9999 — Thu, 16 Jan 2025 19:45:53 GMT

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