cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
faoezdeb Frequent Visitor
Frequent Visitor

Running total with hierarchy (again)

Hello everybody,

 

yes, another running total question. I think, I miss something in my formula or just think the wrong way.

 

My tables look like this:

 

Table 1:

 

Order ID

Value

Client

Signed Date (Date)

Country

Segment

 

Table 2:

 

Country

Segment

Planned Value

Date (Month)

 

Table 3 (Time Dimension):

 

Date

Month

Quarter

Year

 

What I want to do:

 

I want to calculate my running totals for both of my tables per month and then display them in one diagram (to see, if I hit or miss my planned value for this month.

Important/Problem: At the same time I want to have the "Segment" as the legend. So that I can see which segments take the biggest part of my values.

 

--> Whenever I do this, it just adds my segment values to the previous "totals".

 

E.g.

January total:

500 - Segment A

400 - Segment B

100 - Segment C

 

1000 - Total

 

Then it shows me the Value "2000" for January.

 

My current Formula

 

Running Total =

CALCULATE (

    SUM ( 'Table 1 (signed date) 2018'[Value] );

    ALL ( 'Table 1 (signed date) 2018');

   'Table 1 (signed date) 2018[Signed Date]' <= EARLIER ( 'Table 1 (signed date) 2018'[Signed Date] )

)

 

Goal:

Be able to see the running total per month / quarter for Table 1 and Table 2 while being able to display by Segment.

 

I hope I could explain myself correctly...

 

Thanks in Advance

 

Best Regards

Fatih

5 REPLIES 5
rajulshah Member
Member

Re: Running total with hierarchy (again)

Hello @faoezdeb,

 

Can you please share sample data or sample pbix file?

 

Regards.

faoezdeb Frequent Visitor
Frequent Visitor

Re: Running total with hierarchy (again)

Hello @rajulshah

 

here is the pbix and the simplified excel. Had to create a new document...

 

https://www.dropbox.com/sh/050srwrcla9iixp/AAAURf12dPVuJRf5eqwdPvE4a?dl=0

 

Regards

Fatih

 

faoezdeb Frequent Visitor
Frequent Visitor

Re: Running total with hierarchy (again)

Hello @rajulshah

 

there was a small mistake in the previous data, I just updated it.

 

Regards

Fatih

rajulshah Member
Member

Re: Running total with hierarchy (again)

Hello @faoezdeb,

 

Please try the following measure:

 

Running Total =
TOTALYTD(SUM('IS - Order'[Value]),'Date'[Date])

faoezdeb Frequent Visitor
Frequent Visitor

Re: Running total with hierarchy (again)

Hello @rajulshah,

 

thank you for your proposal.

 

Unfortunately, as our initial data is a bit more complicated and we didn't have time to try further solutions - we sticket to a more work intensive python solution.

 

But I also tried your proposal and unfortunately didn't receive the results I expected. The Values don't add up as wished.

 

Kind Regards