cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

pivot tables with growing percentages

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 LengthDay of EventRevenue
313073.08
3214758.9
3315510.14
511655.28
523660.7
531438.3
546076.7
558509.62
61949.08
621098.3
632594.39
642200.29
651698.35
665870.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!

1 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 LengthDay of EventRevenue
31100
32100
33100
31250
32500
331250

 

 

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  
Read my blogs on  



View solution in original post

6 REPLIES 6
Microsoft
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. Smiley Happy

 

Regards

Memorable Member
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  
Read my blogs on  



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?

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 LengthDay of EventRevenue
31100
32100
33100
31250
32500
331250

 

 

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  
Read my blogs on  



View solution in original post

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!

 

TableCapture.PNG

Thank you!  this is awesome.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors