cancel
Showing results for
Did you mean:  Helper V

## Running Sum with steering on anther table

Hi and thanks for reading this.

I have a table with days (0-30) and amount of sold items on days and prices on those days since they can be different.

I have done a measer that calculate the sum for each day ( sold * cost)

Next measure is a Running sum that gives a Total Cost acumulated,

Next Tabel i have is a table that splits the 30 days into 3 boxes (1 = start ,15= middle and 30 = End))

I have a relationship between Monthday and Day ,

I would like to get the running sum value shown on the splits name, the resualt i get is the total value for that day , not the running sum up to that value:

Start = 10

Middle = 218

End = 709

Thanks so much.   2 ACCEPTED SOLUTIONS  Super User

Hi, @Wresen

I am not sure how your whole data model looks like, but please check the below picture and the sample pbix file's link down below.

I think an additional measure is needed. 2: Running cost on where =
VAR selectedday =
MAX ( 'Where'[Day] )
VAR newtable =
FILTER (
SUMMARIZE ( ALLSELECTED(Sold), Sold[Monthday], "@runningcost", [1: Running cost] ),
Sold[Monthday] <= selectedday
)
RETURN
SUMX ( newtable, [@runningcost] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page  Super User

Hi, @Wresen

Thank you for your explanation.

I think it is because of the number 0.

Please try the below measure.

2: Running cost2 on where =
VAR selectedday =
MAX ( 'Where'[Day] )
VAR newtable =
FILTER (
SUMMARIZE (
ALLSELECTED ( Sold2 ),
Sold2[Monthday],
"@runningcost", [1: Running cost 2]
),
Sold2[Monthday] <= selectedday
)
RETURN
IF (
SELECTEDVALUE ( 'Where'[Where] ) <> BLANK (),
SUMX ( newtable, [@runningcost] )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

9 REPLIES 9  Super User

Hi, @Wresen

I am not sure how your whole data model looks like, but please check the below picture and the sample pbix file's link down below.

I think an additional measure is needed. 2: Running cost on where =
VAR selectedday =
MAX ( 'Where'[Day] )
VAR newtable =
FILTER (
SUMMARIZE ( ALLSELECTED(Sold), Sold[Monthday], "@runningcost", [1: Running cost] ),
Sold[Monthday] <= selectedday
)
RETURN
SUMX ( newtable, [@runningcost] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page  Helper V

Hi @Jihwan_Kim and all 🙂

One more question for this measure.

if i have monthday 0 ,monthday -1 and monthday -2. How can i can get all the running sum go to day 1, now i get a blank row for that .Now i cant filter away the blank since that will change the resaults in day 1 ,15 and 30   Super User

Hi, @Wresen

Please check the link down below, which I added one more sample case same as your last description.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page  Helper V

Thanks so much i really appriciate it.

I have one more question if i may.

if i change the day "start" value in table Where to 0 instead of 1 it will jump over that day.

Have tried to modify the measure you have provide but cant seem to get it to work.

I only want the value in the where table to be shown in the answer (or be able to filter them away.

Is this someting that is easy to fix ?    Super User

Hi, @Wresen

Please correct me if I understood your question wrongly.

In this case, I recommend deleting the relationship between Where table and Sold table.

If you delete the relationship, it should work as you expected.

Please check the link down below, that I have deleted the relationship.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page  Helper V

I may have explained bad.

If i change the values in Where table , that "Start" is 0 instead of 1 as it is now the measure will ignore "Start" (the valie is 22 in Sold2 table) and only show Middle = 240 and End =731.
i hop you understand me.

Thanks so much.  Super User

Hi, @Wresen

Thank you for your explanation.

I think it is because of the number 0.

Please try the below measure.

2: Running cost2 on where =
VAR selectedday =
MAX ( 'Where'[Day] )
VAR newtable =
FILTER (
SUMMARIZE (
ALLSELECTED ( Sold2 ),
Sold2[Monthday],
"@runningcost", [1: Running cost 2]
),
Sold2[Monthday] <= selectedday
)
RETURN
IF (
SELECTEDVALUE ( 'Where'[Where] ) <> BLANK (),
SUMX ( newtable, [@runningcost] )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page  Helper V

Thanks so much @Jihwan_Kim for all the time you put down here

I works as  i want now 🙂  Helper V

Thanks so much , it worked perfectly.   