Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ratgdillon
Frequent Visitor

Solution to combine ALLSELECTED with ALLEXCEPT

It looks like I accidentally deleted this post the first time I put it up, so here it is again.

 

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)

 

What the final product should look like:

Account Category5 - Revenue6 - COGS7 - Selling ExpenseTotal
Account51 - Sales52 - Sales Returns53 - Sales DiscountsTotal60 - Wharehouse Purchases61 - Certification Fees62 - Labels & PackagesTotal71 - Advertising72 - Misc. Expense75 - TravelTotal  
CustomerAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NRAmount% of NR
First $  15,000.00100.33% $ (50.00)-0.33% $             -  0.00% $  14,950.00100.00% $ (6,500.00)-43.48% $             -  0.00% $ (500.00)-3.34% $   (7,000.00)-46.82% $ (750.00)-5.02% $ (175.00)-1.17% $ (350.00)-2.34% $ (1,275.00)-8.53% $    6,675.0044.65%
Second $    7,500.00101.35% $          -  0.00% $ (100.00)-1.35% $    7,400.00100.00% $ (3,000.00)-40.54% $ (200.00)-2.70% $             -  0.00% $   (3,200.00)-43.24% $ (200.00)-2.70% $ (200.00)-2.70% 0.00% $     (400.00)-5.41% $    3,800.0051.35%
Total $  22,500.00100.67% $ (50.00)-0.22% $ (100.00)-0.45% $  22,350.00100.00% $ (9,500.00)-42.51% $ (200.00)-0.89% $ (500.00)-2.24% $ (10,200.00)-45.64% $ (950.00)-4.25% $ (375.00)-1.68% $ (350.00)-1.57% $ (1,675.00)-7.49% $  10,475.0046.87%
2 REPLIES 2
Stachu
Community Champion
Community Champion

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1 Column2
A 1
B 2.5

Definitely include 'GL Entries' and any other table that would be relevant here



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

I just added a table with some Raw Data and a table with the end result that Power BI should come up with. You can treat the Product column as an slicer that can be selected by the user. Thanks!

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.