Running Sum with steering on anther table

[Wresen]

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

Link to file:
https://drive.google.com/file/d/1qCRJbg6CP0WpeXZn0U5Xmtt8hyQBfhau/view?usp=sharing

Thanks so much.

[Jihwan_Kim · Accepted solution]

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

https://www.dropbox.com/s/gh3m1yucmn5eh74/Sold.pbix?dl=0 

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.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Jihwan_Kim · Accepted solution]

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

https://www.dropbox.com/s/gh3m1yucmn5eh74/Sold.pbix?dl=0 

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.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Wresen]

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

[Jihwan_Kim]

Hi, @Wresen 

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

https://www.dropbox.com/s/cyl3vdfndhdaef1/Sold%20%281%29.pbix?dl=0 

[Wresen]

Hi @Jihwan_Kim 

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 ?

[Jihwan_Kim]

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.

https://www.dropbox.com/s/cyl3vdfndhdaef1/Sold%20%281%29.pbix?dl=0 

[Wresen]

Hi @Jihwan_Kim 

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.

[Jihwan_Kim · Accepted solution]

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

)

[Wresen]

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

I works as  i want now 🙂

[Wresen]

Hi @Jihwan_Kim 

Thanks so much , it worked perfectly.