pivot tables with growing percentages

[Anonymous]

Hello everyone,

I have a question about growing percentages.  The business case is...

We have events of different lengths (number of days), we have average revenues per day, grouped by the day of the event...

The table looks like this...

Event Length	Day of Event	Revenue
3	1	3073.08
3	2	14758.9
3	3	15510.14
5	1	1655.28
5	2	3660.7
5	3	1438.3
5	4	6076.7
5	5	8509.62
6	1	949.08
6	2	1098.3
6	3	2594.39
6	4	2200.29
6	5	1698.35
6	6	5870.65

I want to build a table or a column onto this table which can be used to show "By day x of the event, we should have made x% of your revenue"

Thanks in advance!

[v-ljerr-msft]

Hi @Anonymous,

Have you tried the solution provided by @SteveCampbell above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[SteveCampbell]

You will need an event ID. In DAX, there is no concept of ordering like in excel.

Assuming you give and event ID called Event_ID to each event, you could do:

Percent of Revenue=

VAR Current_Day = max(Table1[Day of Event])
Var Current_Event = max(Table1[Event_ID])

RETURN

Divide(
  CALCULATE(sum(Table1[Revenue]) 
	, Table1[Day of Event] <= Current_Day
        , Table1[Event_ID] = Current_Event 
    )
,
  CALCULATE(sum(Table1[Revenue]) 
        , allexcept(Table1, Table1[Event_ID])
   )
 

[Anonymous]

hi Steve,

Im not sure I understand.  Each event is separate, but I want to agrgigate each event length (with the same event length) into one.  This should give me an average per day for each event length...  which can the be used to predict the outcome of a future event of same length...

Does that make sense?

[SteveCampbell · Accepted solution]

I think I misunderstood. The percent of revenue measure will give you a cumulative total of each day by event:

Percent of Revenue=

VAR Current_Day = max(Table1[Day of Event])
Var Current_Event = max(Table1[Event_ID])

RETURN

Divide(
  CALCULATE(sum(Table1[Revenue]) 
	, Table1[Day of Event] <= Current_Day
        , Table1[Event_ID] = Current_Event 
    )
,
  CALCULATE(sum(Table1[Revenue]) 
        , allexcept(Table1, Table1[Event_ID])
   )

I think the measure you are after is diffferent. I am assuming you are summing all total events, for example imagine you had two events with length of 3:

Event Length	Day of Event	Revenue
3	1	100
3	2	100
3	3	100
3	1	250
3	2	500
3	3	1250

I believe the calculation you want is to see the total sum of both events (2300), and find what percent was on day 1 (350, 15.2%), and ect for each day. In that case, you could do this:

Average Percent of Revenue by Day = 

VAR Current_Day = max(Table1[Day of Event])
Var Current_Length = max(Table1[Event Length])

RETURN

Divide(
  CALCULATE(sum(Table1[Revenue]) 
	, Table1[Day of Event] = Current_Day
        , Table1[Event Length] = Current_Length 
    )
,
  CALCULATE(sum(Table1[Revenue]) 
        , allexcept(Table1, Table1[Event Length])
   )
 )

[Anonymous]

Hi Steve, thanks for your help on this, it's been hugely helpful.

It's working great as a measure, how would I make this into a table?  With the results looking something like this?  Once it's in a table I can use this to create a predictuive model based upon past sales.  Thanks in advance for your help.  Happy new year!

[Anonymous]

Thank you!  this is awesome.