Running Total by Store (instead of by date)

[jbuzaglu]

Basically I have SKUs by different stores, and need the running total from most sold to least sold.

From the screenshot, I need to have the running total of the "Unit Weight", descending. And reseting by each store name. Aditionally, I can't use the "sum" function on Unit Weight since it is a mesure from a live connection (tabular model).

Any help or leads on this would be GREATLY appreciated

[FreemanZ]

hi @jbuzaglu 

The logic is very similar. Supposing you have a table like:

try to plot a table visual with the Store column and a measure like:

Measure = 
SUMX(
    FILTER(
        ALL(TableName),
        TableName[Sales]>=MAX(TableName[Sales])
    ),    
    TableName[Sales]
)

it worked like:

[jbuzaglu]

When I try to create the measure, I get this error. Basically it is not a table but measures

And aditionally, I can't use the sum function on a measure. This is why it is complicated to me, I want this value to change as I slice thorugh different dates.

Thank you for replying

[FreemanZ]

hi @jbuzaglu 

could you please abstract your case and reproduct it with simplified sample dataset?

[jbuzaglu]

I can certainly replicate it, the only problem is that this is a live connection, and this is the complexity of my case.

But in simplified fashion I have the following:

Store    SKU     Units Sold      Unit Weight         Running Total (Desired Measure)

A          01A          4                 4/13=30.8%                     30.8%

A          02B          4                 4/13=30.8%                     61.6%

A          03B          3                 3/13=23.1%                     84.7%

A          02A          2                 2/13=15.3%                    100%

B          01A          5                  5/8=62.5%                       62.5%

B          09A          2                  2/8=25%                          87.5%

B          02B          1                  1/8=12.5%                       100%

C          01A         3                  3/6=50%                            50%

C          02B         2                  2/6= 33.3%                       83.3%

C          03B         1                  1/6=16.67%                      100%

I would like the measure to change as I change dates, and loads new data thorugh my live connection.

Thank you FreemanZ for your kind help

[FreemanZ]

hi @jbuzaglu 

try to plot a table visual a measure like:

RT = 
SUMX(
    FILTER(
        ALL(TableName),
        TableName[Store] = MAX(TableName[Store])
        &&TableName[UnitsSold]>=MAX(TableName[UnitsSold])
    ),
    TableName[Weight]
)

[jbuzaglu]

Thank you for your response, I strongly think this is the way, however I get this error since the units sold is a changing measure depending on date:

Any clue how I can overcome this error?

Again, thank you for your patience regarding this.

[FreemanZ]

if  you prefer CALCULATE, try like:

Measure2 = 
CALCULATE(
    SUM(TableName[Sales]),
    FILTER(
        ALL(TableName),
        TableName[Sales]>=MAX(TableName[Sales])  
    )
)

[jbuzaglu]

Thank you for replying andhiii079845, yes except it would be decresing from highest store units to lowest. So in the example you sent, store C would be viceversa how it is performing the running total. Any help I would be forever greateful. 

[andhiii079845]

Do you want a Table like this? Unit weight is a running total (cummaltive) per store?

Store	Unit per Store	Unit CY	Unit weight
A	78	3	4% (3/78)
A	78	3	8% (6/78)
A	78	3	12% (9/78)
C	78	3	4% (3/78)
C	78	6	12% (9/78)
C	78	24	42% (33/78)