topic Re: Running Total by Store (instead of by date) in DAX Commands and Tips

Running Total by Store (instead of by date)

jbuzaglu — Tue, 28 Feb 2023 20:26:52 GMT

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

Re: Running Total by Store (instead of by date)

andhiii079845 — Wed, 01 Mar 2023 07:08:14 GMT

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)

Re: Running Total by Store (instead of by date)

jbuzaglu — Wed, 01 Mar 2023 14:01:42 GMT

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.

Re: Running Total by Store (instead of by date)

FreemanZ — Wed, 01 Mar 2023 14:25:18 GMT

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:

Re: Running Total by Store (instead of by date)

FreemanZ — Wed, 01 Mar 2023 14:28:13 GMT

if you prefer CALCULATE, try like:

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

Re: Running Total by Store (instead of by date)

jbuzaglu — Wed, 01 Mar 2023 14:56:31 GMT

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

Re: Running Total by Store (instead of by date)

FreemanZ — Wed, 01 Mar 2023 15:00:23 GMT

hi jbuzaglu

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

Re: Running Total by Store (instead of by date)

jbuzaglu — Wed, 01 Mar 2023 16:04:36 GMT

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

Re: Running Total by Store (instead of by date)

FreemanZ — Thu, 02 Mar 2023 13:14:45 GMT

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] )

Re: Running Total by Store (instead of by date)

jbuzaglu — Thu, 02 Mar 2023 15:57:52 GMT

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.