Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Is this what you are expecting the result to be
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi.
I've followed the proposed solution to this thread but it does not seem to work for me. I have a PnL Database table that looks like this:
Time | Currency | Entity | AccountName | Value |
Revenue | 1,000 | |||
Cost of sales | 400 | |||
SG&A | 300 |
and I cannot get Total Revenue to appear on every P&L account using the following:
TtlRevenue =
VAR Revenue = CALCULATE (SUM('PnL Database'[value], FILTER('PnL Database'[Account Name]="Revenue"))
RETURN CALCULATE(Revenue, ALL('PnL Database'[Account Name]))
The result I get is as follows, the Ttl Revenue figure does appear against the other PnL line.
Account Name | Value | TtlRevenue |
Revenue | 1,000 | 1,000 |
Cost of Sales | 400 | |
SG&A | 300 |
Any advice? Thanks in advance.
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.
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).
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.
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.
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 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@codexteroif you can confirm the image i posted is the result you are expecting that will be helpful to get you the solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Is this what you are expecting the result to be
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |