- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculating Percentage of Revenue

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

codextero

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
09:45 AM

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

Account | Year | Month | Value |

1 | 2010 | 1 | 8 |

1 | 2010 | 2 | 7 |

2 | 2010 | 1 | 6 |

2 | 2010 | 2 | 5 |

3 | 2010 | 1 | 4 |

3 | 2010 | 2 | 3 |

4 | 2010 | 1 | 2 |

4 | 2010 | 2 | 1 |

Table Chart_of_Accounts

Account | Level 1 | Level 2 |

1 | Revenue | Lemonaide |

2 | Revenue | Popsicles |

3 | Expense | Lemons |

4 | Expense | Ice |

Putting this data in a pivot table is clean enough.

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.

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.

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

parry2k

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:17 AM

parry2k

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:33 AM

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 )

8 REPLIES 8

Moscuba

Member

Re: Calculating Percentage of Revenue

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:02 AM

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.

parry2k

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:17 AM

codextero

Frequent Visitor

Re: Calculating Percentage of Revenue

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:21 AM

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"*)

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).

parry2k

Super User

Re: Calculating Percentage of Revenue

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:25 AM

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

codextero

Frequent Visitor

Re: Calculating Percentage of Revenue

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:31 AM

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.

Highlighted
##

codextero

Frequent Visitor

Re: Calculating Percentage of Revenue

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:33 AM

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.

parry2k

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:33 AM

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 )

Moscuba

Member

Re: Calculating Percentage of Revenue

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
11:37 AM

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.