Running total based on other columns value

Question

I need to calculate running total of forecast amount starting from maximum value, based on two stock locations and once for each product (product numbers are repetitive due to stock locations). e.g product no "1" should be used once for running total.

My first code, which didn't sum the same forecast amounts independently based on item no.

RunningTotal1 = 
VAR
CurrentAmount= Listing[Forecast Amount]
RETURN
SUMX (
FILTER (
Listing;
Listing[Forecast Amount]>= CurrentAmount);
Listing[Forecast Amount])

My second code where running total was based on stock location, it calculates for each location independently still didn't sum the same forecast amounts independently based on item no.

RunningTotal2 = 
VAR CurrentAmount = Listing[Forecast Amount]
VAR Location = Listing[Stock Location]
RETURN
SUMX (
FILTER (
Listing;
Listing[Stock Location] = Location &&
Listing[Forecast Amount]>= CurrentAmount);
Listing[Forecast Amount])

But when I add second location to my formula it gives an error.

"DAX comparison operations do not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values."

RunningTotal3 = 
VAR CurrentAmount = Listing[Forecast Amount]
VAR LocationW = Listing[Stock Location] = "Warehouse"
VAR LocationT = Listing[Stock Location] = "Total Stock"
RETURN
SUMX (
FILTER (
Listing;
Listing[Stock Location] = LocationW ||
Listing[Stock Location] = LocationT &&
Listing[Forecast Amount]>= CurrentAmount);
Listing[Forecast Amount])

What I expect is

Hello @RADO,

I tried to add as measure but couldn't succeed therefore I added as a new column. I realize my code is wrong, in your image item no 5 and 6 have different number. my formula is

Forecast Index = RANKX(Listing;Listing[Forecast Amount])

here's the result Forecast Index

regards

I would suggest you to rephrase you question, writing it more simple and clear. Additionally, to increase chances of receiving an answer you might present the current situation and the expected result. I am sure there will be someone able to explain you how to transit from the initial situation to the final goal :) — Seymour
Hello @Seymour thank you for your help. I hope it's simple now :) — coolidge

RADO RADO · Accepted Answer · 2019-06-25T21:41:06

You need to add a column to your "Listing" table that defines order for the running total. You can't use Forecast Amount for that, because there are cases where different items have the same amounts (for example, items 66 and 99), and there is no way to resolve these ties (which items should be accumulated first - 66 or 99? No way to tell).

Often, date/time fields are used for that, but if you don't have them, you can add an index based on whatever rules you need. For this example, I manually added "Forecast Index" as follows:

Forecast index here is simply a sorting order - if you sort by it, you will get the layout exactly matching your "desired result" table.

Then, create a measure:

RT Expected = 
VAR Current_Index =  MAX ( Listing[Forecast Index] )
VAR Summary_Table =
    SUMMARIZE (
        FILTER (
            ALL ( Listing ),
            Listing[Forecast Index] <= Current_Index  && 
            Listing[Stock Location] <> "Without Stock" ),
        Listing[Item No],
        Listing[Forecast Amount] )
RETURN
    SUMX ( Summary_Table, Listing[Forecast Amount] )

Result:

Note: If you don't want to see Items 2 and 4, simply remove them from the visual filter.

How it works:

First, we use FILTER to create a virtual table that a) ignores "Without Stock" locations, and b) keeps only forecast amounts that we need for the running total;
Second, we use SUMMARIZE to group that virtual table by Item No and Forecast Amount. The grouping eliminates duplicates;
Finally, we use SUMX to iterate the de-duplicated table, and sum up all relevant amounts.

Edit:

You can create a proper index using PowerQuery:

In your left panal, Go to "Data", and select "Listing" table;
Right-click the table, and select "Edit Query". Power BI will take you to the Power Query window;
In Power Query, first, sort column "Forecast Amount" Descending. Then, sort column "Item No" Ascending. This will arrange your records in the same order as you have them in your picture;
Finally, in Power Query window, go to "Add Column", then "Index Column", select "From 1". Power Query will create an index column for you, and name it "Index". You can rename it as needed (i.e, to "Forecast Index");
Click "Close and Apply", and then build the measure I suggested using this new index column as an input.

In case these instructions are not clear, refer to this article:

https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/

Running total based on other columns value

1 Answers