topic Re: Sum columns not working in DAX Commands and Tips

Sum columns not working

1125NEWBIUSER — Sun, 11 Sep 2022 02:41:14 GMT

Hi,

I have got the hardest part of my DAX formulas working to get my running totals, now I want to add the 4 calculated fields I created together. I did a sumx formula, in picture below but it is not totalling correctly. If you manually add, you should have -149 but I am getting the value of 111 from the field. What did I do wrong here?

Second question, I have running totals and I want to keep this life to date, independent of date filters. Meaning if I just grabbed todays date in filter, I want the totals to remain the same. But currently, it only calculates based on the dates I am pulling into table. So i cannot just see the RT (Running Total) Orders at a point in time. Is this possible? The formula for one below.

RT Orders =

Var LastSaleDate = Calculate( LastDate( 'orders/wip'[Order date]),ALL('Orders/WIP'[Quantity]))

RETURN

IF( SELECTEDVALUE( 'orders/wip'[Order date]) > LastSaleDate, BLANK(),

-CALCULATE( sum('Orders/WIP'[Quantity]) ,

FILTER( ALLSELECTED( 'Orders/WIP'),

'orders/wip'[Order date] <= MAX ('orders/wip'[Order date]) && 'Orders/WIP'[Invoiced] <>"Return"

&& 'Orders/WIP'[SaleTransType] = "Standard")))

Thanks! Chris

Re: Sum columns not working

Greg_Deckler — Sun, 11 Sep 2022 10:57:57 GMT

1125NEWBIUSER If you want to add four fields together, just add them together, no need for a DAX function:

[Measure] + [Measure 1] + [Measure 2] + [Measure 3]

Same thing for columns.

I feel like you are overcomplicating your running total. I just posted a Better Running Total that demonstrates a super simple approach to this. https://community.powerbi.com/t5/Quick-Measures-Gallery/Better-Running-Total/td-p/2755666

Would need sample data and expected output to go further.

Re: Sum columns not working

1125NEWBIUSER — Sun, 11 Sep 2022 12:55:16 GMT

Greg_Deckler for the first, that did the trick, thank you very much.

For the second item, that worked as well. I added my additional features to VAR __Table and it matches the other formula I used. Is the below enough for the data set example? Below is Table 1 (Left), I have the RT versus the orders per day. Table 2 (Right), I have same thing but filtered to just see this month. The running total adjusts to filtered date of what I want to show in chart versus I want the formula to be static so I can see the left results but at a period of time.

Thanks, Chris

Re: Sum columns not working

Greg_Deckler — Sun, 11 Sep 2022 14:45:32 GMT

1125NEWBIUSER Really need the source data as text in a table so that it can be copied and pasted into an Enter Data query. I don't like to do that much typing.

First check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Sum columns not working

1125NEWBIUSER — Mon, 12 Sep 2022 00:54:28 GMT

Greg_DecklerI believe I have added the based data/table as needed now. Also show examples of what I am currently getting versus what I hope to achieve.

What I have:

Order Date	Quantity
3/1/2019	150
12/1/2020	500
5/1/2021	100
9/1/2022	450

What I want is a running total, current formula used:

Better RT Orders =

VAR __Date = MAX('Orders/WIP'[Order date])

VAR __Table = FILTER(ALLSELECTED('Orders/WIP'),[Order date] <= __Date && [Invoiced] <> "Return" && [SaleTransType] = "Standard")

RETURN

(- SUMX(__Table,[Quantity]))

What I get:

Order Date	Quantity
3/1/2019	150
12/1/2020	650
5/1/2021	750
9/1/2022	1200

My current formula is doing this. But it is dynamic. So when I then put into a visual, lets say I only want to see this month, I currently see:

Order Date	Quantity
9/1/2022	450

Because the visual is filtered by date, the formula is updating to only show me running total for this month. What i want to see is:

Order Date	Quantity
9/1/2022	1200

Is it possible to update my DAX so it calculates based on the total tables, not just on the visual filters that currently are driving these totals. I want to have a life to date running total that I can plug in to see at any one point in time.

Thanks, Chris