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

Calculating Percentage of Revenue

So this is a problem that's been stumping me for a while. 

 

The task is to calculate total revenue, then calculate other accounts as a percentage of revenue.

 

Take this dummy data set

 

Table Balances

 

AccountYearMonthValue
1201018
1201027
2201016
2201025
3201014
3201023
4201012
4201021

 

Table Chart_of_Accounts

 

AccountLevel 1Level 2
1RevenueLemonaide
2RevenuePopsicles
3ExpenseLemons
4ExpenseIce

 

Putting this data in a pivot table is clean enough.

Pivot table 1.png

 

 

Revenue for any month would be the sum of account 1 and 2, as they have the Level 1 filter of "Revenue".

 

I thought that a measure would be the best way to calculate an account as a percentage of revenue, so I wrote a measure for that purpose.

 

Percent_of_Revenue = DIVIDE(CALCULATE(sum(Balances[Value])),CALCULATE(sum(Balances[Value]),Chart_of_accounts[Level 1]="Revenue")) 

 

The idea goes you sum up all account entries that have a Level 1 of "revenue" and divide the value by revenue to get percent of revenue. However, the measure didn't quite work that way.

 

 

Pivot table 2.png

It's clear from this that the measure is filtering by reporting level, so the "total revenue" part of the measure is never actually calculating the total revenue, but only for that specific filter result.

 

I think this can be corrected by using ALLEXCEPT in the measure, but I've been unable to get it to work.

 

Edit : can't attach a .pbix to this post, account is probably too new.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Calculating Percentage of Revenue

Is this what you are expecting the result to be

 

revenue.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Super User
Super User

Re: Calculating Percentage of Revenue

so pretty much what I posted was correct result and that's what you are looking for

 

here are my dax expression, I like to break those in smaller piece for readbility

 

Total Value = SUM ( Data[Value] )

Total Revenue = CALCULATE( [Total Value], FILTER ( ALLSELECTED( Chart ), Chart[Level 1] = "Revenue" ) )

% of Revenue = DIVIDE( [Total Value], [Total Revenue], 0 )





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

8 REPLIES 8
Moscuba Member
Member

Re: Calculating Percentage of Revenue

Use ALLSELECTED.

 

Here is my formula that works (SalesSUM is a Measure that is Sum of Sales):

Percentage Of Total = 'Order_Line_Invoice'[SalesSUM]/CALCULATE(sum('Order_Line_Invoice' [Sales]),ALLSELECTED())

 

Injecting what I think are your fields:

Percentage Of Total = 'sum(Balances'[Value]/CALCULATE(sum('Balances' [Value]),ALLSELECTED())

 

I hope it works.

Super User
Super User

Re: Calculating Percentage of Revenue

Is this what you are expecting the result to be

 

revenue.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

codextero Frequent Visitor
Frequent Visitor

Re: Calculating Percentage of Revenue

I think I'm on the right path, with an added filter to the measure, I was able to get part of what I wanted.

 

Percentage_Of_Total_mod1 = sum('Balances'[Value])/CALCULATE(sum('Balances'[Value]),ALLSELECTED(),Chart_of_accounts[Level 1]="Revenue")

 

Pivot Table 3.png

The subcategories are being correctly divided by the revenue.

 

However, ALLSELECTED removes the year and month filters, so data is only correct at the highest level, and if you drill down, the numbers are being divided by the total revenue across all months rather than by the monthly revenue. I'm thinking that the correct implemention involves a way for the measure to ignore reporting levels, but keeping all other filters (e.g. month/year).

 

pivot table 4.png

Super User
Super User

Re: Calculating Percentage of Revenue

@codexteroif you can confirm the image i posted is the result you are expecting that will be helpful to get you the solution.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





codextero Frequent Visitor
Frequent Visitor

Re: Calculating Percentage of Revenue

I got it.

 

Percentage_Of_Total_mod1 = sum('Balances'[Value])/CALCULATE(sum('Balances'[Value]),ALL(Chart_of_accounts[Level 1],Chart_of_accounts[Level 2]),Chart_of_accounts[Level 1]="Revenue")

 

returns the correct results.

 

pivot table 5.png

codextero Frequent Visitor
Frequent Visitor

Re: Calculating Percentage of Revenue

Yep that's exactly what I was looking for. I found my own solution to the problem, but I'd be intrested to see yours.

Highlighted
Super User
Super User

Re: Calculating Percentage of Revenue

so pretty much what I posted was correct result and that's what you are looking for

 

here are my dax expression, I like to break those in smaller piece for readbility

 

Total Value = SUM ( Data[Value] )

Total Revenue = CALCULATE( [Total Value], FILTER ( ALLSELECTED( Chart ), Chart[Level 1] = "Revenue" ) )

% of Revenue = DIVIDE( [Total Value], [Total Revenue], 0 )





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

Moscuba Member
Member

Re: Calculating Percentage of Revenue

The calculation I posted works with filters. 

 

I recommend making a measure of the Sum of Revenue and using that instead of the root data. In that you'd put the filter "Chart of Accounts" criteria. That may be why the formula is not working for you.

 

Measures that pre-sum commonly used numbers work well and save future work.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 241 members 2,393 guests
Please welcome our newest community members: