cancel
Showing results for
Did you mean:
Helper III

## pivot tables with growing percentages

Hello everyone,

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"

1 ACCEPTED SOLUTION
Memorable Member

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

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!
Stay up to date on

6 REPLIES 6
Microsoft

Hi @pchapple,

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

Memorable Member

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

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!
Stay up to date on

Helper III

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?

Memorable Member

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

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!
Stay up to date on

Helper III

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!

Helper III

Thank you!  this is awesome.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!