cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ratgdillon
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.AccountAccount CategoryCustomerProductAmount
12142451 - Sales5 - RevenueFirstFood $  10,000.00
24674751 - Sales5 - RevenueFirstDrink $    5,000.00
95827351 - Sales5 - RevenueSecondFood $    7,500.00
99209353 - Sales Discounts5 - RevenueSecondFood $     (100.00)
23536752 - Sales Returns5 - RevenueFirstFood $        (50.00)
23257860 - Wharehouse Purchases6 - COGSFirstFood $  (4,000.00)
34786460 - Wharehouse Purchases6 - COGSFirstDrink $  (2,500.00)
46906461 - Certification Fees6 - COGSSecondFood $     (200.00)
26747560 - Wharehouse Purchases6 - COGSSecondFood $  (3,000.00)
46732362 - Labels & Packages6 - COGSFirstDrink $     (500.00)
91836571 - Advertising7 - Selling ExpenseFirstFood $     (750.00)
13576471 - Advertising7 - Selling ExpenseSecondFood $     (200.00)
24356872 - Misc. Expense7 - Selling ExpenseFirstDrink $        175.00
35467872 - Misc. Expense7 - Selling ExpenseSecondFood $     (200.00)
86543275 - Travel7 - Selling ExpenseFirstDrink $     (350.00)

 

Final Product:

PBI comm supp ex.png

1 ACCEPTED SOLUTION
ratgdillon
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]))

 

 

View solution in original post

3 REPLIES 3
ratgdillon
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]))

 

 

Greg_Deckler
Super User
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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

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

@ratgdillon ,

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors