Solution to combine ALLSELECTED with ALLEXCEPT

ratgdillon — Mon, 31 Aug 2020 20:12:33 GMT

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

What the final product should look like:

Account Category	5 - Revenue	6 - COGS	7 - Selling Expense	Total
Account	51 - Sales	52 - Sales Returns	53 - Sales Discounts	Total	60 - Wharehouse Purchases	61 - Certification Fees	62 - Labels & Packages	Total	71 - Advertising	72 - Misc. Expense	75 - Travel	Total
Customer	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR	Amount	% of NR
First	$ 15,000.00	100.33%	$ (50.00)	-0.33%	$ -	0.00%	$ 14,950.00	100.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.00	44.65%
Second	$ 7,500.00	101.35%	$ -	0.00%	$ (100.00)	-1.35%	$ 7,400.00	100.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.00	51.35%
Total	$ 22,500.00	100.67%	$ (50.00)	-0.22%	$ (100.00)	-0.45%	$ 22,350.00	100.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.00	46.87%

Re: Solution to combine ALLSELECTED with ALLEXCEPT

Stachu — Mon, 31 Aug 2020 18:50:51 GMT

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

Re: Solution to combine ALLSELECTED with ALLEXCEPT

ratgdillon — Mon, 31 Aug 2020 20:09:40 GMT

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!

topic Re: Solution to combine ALLSELECTED with ALLEXCEPT in DAX Commands and Tips

Solution to combine ALLSELECTED with ALLEXCEPT

Re: Solution to combine ALLSELECTED with ALLEXCEPT

Re: Solution to combine ALLSELECTED with ALLEXCEPT