Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wresen
Post Patron
Post Patron

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.