cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating Percentage of Net Revenue in a POP&L

I have improved my table and example data. Hopefully it helps.

I am working on a POP&L that displays a dollar value by customer (row) and account number (column) and I want to include a column that calculates percent of net revenue (dollar value/net revenue). The accounts are broken out by categories like revenue, selling expense, and cost of goods sold, so a customer's net revenue represents the sum of values in that customer's revenue category. The net revenue measure needs to ignore the column it is in because it should always sum the revenue category, even when calculating percent of net revenue for an account in a category like cost of goods sold. It should also take into account the row it is in (customer) and any explicit filters applied.

ALLSELECTED() retains only explicit filters and ALLEXCEPT() retains any specified row filters, but I am having trouble combining them so I can calculate Net Revenue by customer, year filter, month filter, etc, but not by account category (because account category for net revenue should always refer to the revenue account). I tried using an inner join to combine the commands, but the result usually only applies one of the functions.

This is the function I tried using:
Net Revenue by Customer = CALCULATE(SUM([Amount]), FILTER(NATURALINNERJOIN(ALLEXCEPT('GL Entries', 'GL Entries'[Customer]), ALLSELECTED('GL Entries'), 'GL Entries'[Account Category] = "Revenue"))
I am wondering whether this DAX function is incompatible with my purpose for some reason.

I am looking for a formula for Net Revenue by Customer, where [% Net] = DIVIDE([Amount], [Net Revenue by Customer]). One way to know if it is working correctly is if the percent of net revenue in the total of the revenue column is 100%.

Any ideas? If you have any clarifying questions please ask, I will be paying attention to the comments.
Thanks

Raw Data:

 Entry No. Account Account Category Customer Product Amount 121424 51 - Sales 5 - Revenue First Food \$  10,000.00 246747 51 - Sales 5 - Revenue First Drink \$    5,000.00 958273 51 - Sales 5 - Revenue Second Food \$    7,500.00 992093 53 - Sales Discounts 5 - Revenue Second Food \$     (100.00) 235367 52 - Sales Returns 5 - Revenue First Food \$        (50.00) 232578 60 - Wharehouse Purchases 6 - COGS First Food \$  (4,000.00) 347864 60 - Wharehouse Purchases 6 - COGS First Drink \$  (2,500.00) 469064 61 - Certification Fees 6 - COGS Second Food \$     (200.00) 267475 60 - Wharehouse Purchases 6 - COGS Second Food \$  (3,000.00) 467323 62 - Labels & Packages 6 - COGS First Drink \$     (500.00) 918365 71 - Advertising 7 - Selling Expense First Food \$     (750.00) 135764 71 - Advertising 7 - Selling Expense Second Food \$     (200.00) 243568 72 - Misc. Expense 7 - Selling Expense First Drink \$        175.00 354678 72 - Misc. Expense 7 - Selling Expense Second Food \$     (200.00) 865432 75 - Travel 7 - Selling Expense First Drink \$     (350.00)

Final Product:

1 ACCEPTED SOLUTION
Frequent Visitor

So I think I actually figured it out myself. By using the filter function of CALCULATE I was able to target [Net Revenue by Customer] to "5 - Revenue" and by using REMOVEFILTERS I was able to apply the calculation to all columns, regardless of account category or account.

``````Net Revenue by Customer =
CALCULATE([Amount],
'GL Entries'[Account Category] = "5 - Revenue",
REMOVEFILTERS('GL Entries'[Account], 'GL Entries'[Account Category]))``````

3 REPLIES 3
Frequent Visitor

So I think I actually figured it out myself. By using the filter function of CALCULATE I was able to target [Net Revenue by Customer] to "5 - Revenue" and by using REMOVEFILTERS I was able to apply the calculation to all columns, regardless of account category or account.

``````Net Revenue by Customer =
CALCULATE([Amount],
'GL Entries'[Account Category] = "5 - Revenue",
REMOVEFILTERS('GL Entries'[Account], 'GL Entries'[Account Category]))``````

Super User

@ratgdillon - Maybe:

``````Net Revenue Measure =
VAR __Customer = MAX([Customer])
RETURN
SUMX(FILTER(ALL('Table'),[Customer]=__Customer&&[Account Category]="5 - Revenue"),[Amount])
``````

Similar for costs.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

Not very clear

try Measure like

divide(Sum(Table[Amount]),calculate(Sum(Table[Amount]), filter(all(Table[Account Category]),Table[Account Category] =max(Table[Account Category]))))
or
divide(Sum(Table[Amount]),calculate(Sum(Table[Amount]), removefilters(Table[Account Category])),filter((Table[Account Category]),Table[Account Category] =max(Table[Account Category]))))

But this works best when you have a separate table for Account Category joined with this table

like

divide(Sum(Table[Amount]),calculate(Sum(Table[Amount]), filter(all(Account),Account[Account Category] =max(Account[Account Category]))))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors