Forum Discussion

1125NEWBIUSER's avatar
1125NEWBIUSER
Icon for Helper I rankHelper I
4 years ago
Solved

Sum columns not working

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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

    • 1125NEWBIUSER's avatar
      1125NEWBIUSER
      Icon for Helper I rankHelper I
      4 years ago

      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