cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wresen
Helper V
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

 

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

 

 

Thanks so much.

 

Sold1.pngSold2.pngSold3.png

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
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.

 

Picture8.png

 

 

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.


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

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


View solution in original post

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


View solution in original post

9 REPLIES 9
Jihwan_Kim
Super User
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.

 

Picture8.png

 

 

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.


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

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


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

Sold4.png

 

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 

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


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 ?

 

sold5.png

 

sold6.png

 

 

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 

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


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.

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


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

 

I works as  i want now 🙂

Hi @Jihwan_Kim 

 

Thanks so much , it worked perfectly.

 

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors