Help with Switch Measure and Subtotals

[Anonymous]

Hi,

I am trying to use dax to create an income statement and am having trouble getting the totals to work.  I want the measure to give me the line item value if the index (or summary index) does not indicate a subtotal.

Basically I am giving every line item an index and then each summary line item a second index. 

Here is what I am hoping to get with each line item displayed and then the total.   I need this to be in Excel and so some of the tricks that I have found only work in Power BI.  Can someone please help me with this measure?

Report Name	ABS PTD ACTUAL
Sales	112,664,030
Sales Returns/Allowances	10,017,049
Sales-Direct	45,168,896
Sales-Discounts	11,919,233
Sales-Freight	1,118,574
Sales-Labor	3,066,250
Sales-Service Local Buy-outs	283
Sales-Service Mileage	293,195
Sales-Service Misc Material	79,618
Sales-Service Per Diem	59,887
Sales-Service Special Services	9,812
Sales-Subcontractor	29,918
Total Sales	627,130
Net Sales	141,181,310

Here is what I am currently getting using the following measure. The tables are not joined.

=VAR MaxItem=max(MASTER_IS_TEMPLATE[Original Index])
Return( Switch(True(),
MaxItem=8,[Net Sales],
MaxItem=24,[Total Total COST Of Goods Sold],
CALCULATE( [ABS PTD ACTUAL],
FILTER( 'MASTER_IS_TEMPLATE', 'MASTER_IS_TEMPLATE'[Original Index] =MaxItem ) )))

Original Index	IS Item	Summary Index	measure 3
1	Sales	0	$         8,083,007.98
2	Sales-Direct	0	$         8,083,007.98
3	Sales-Subcontractor	0	$         8,083,007.98
4	Sales - Labor	0	$         8,083,007.98
5	Sales - Freight	0	$         8,083,007.98
6	Total Sales	0	$         8,083,007.98
7	Sales Returns & Allowances	0	$         8,083,007.98
8	Net Sales	1	$ (141,181,310.07)

[Anonymous]

Hi,

To give even more perspective. I am trying to just get a simple income statement in Excel, with blank rows where blank is indicated.  I need the subtotals (both $ and % to calculate accordingly).  As an example the first group sales, will total into "Net Sales".  It feels like there has to be a way to do an income statement like this in Excel.  Thanks

Name	Index	Group Index	Blank
Sales	1	1
Sales-Direct	2	1
Sales-Subcontractor	3	1
Sales - Labor	4	1
Sales - Freight	5	1
Total Sales	6	1
Sales Returns & Allowances	7	1
Net Sales	8
	9		blank9
Cost of Goods Sold	10	2
Cos-Direct	11	3
Cos-Subcontractor	12	2
Cost of Goods Sold - Labor	13	2
Cos-Freight	14	2
Sales COGS	15	2
Sales Contribution	16	2
% Sales	17
	18		blank18
Loss/(Gain) on Inventory	19	3
Vendor Rebates	20	3
Early Pay Discounts	21	4
Freight Variance	22	3
Transfer Freight	23	3
Total Cost of Goods Sold	24
	25		blank25
Gross Profit	26
Gross Profit %	27

[Anonymous]

Hi @Anonymous ,

Depending on your description, you want to return specific measures [Net Sales] and [Total Total COST Of Goods Sold] when the original index is 8 or 24 (I can only consider them as measures for the time being, since there is no other information about them), and the other original index return the corresponding row of [ABS PTD ACTUA].

If my understanding is correct, I suggest you compare the ReportName and IS Item in CALCULATE to get the corresponding row of [ABS PTD ACTUA].

Measure3 = VAR MaxItem=max('Table (2)'[Original Index])//Table2 is a table with Original Index column
Return
Switch(TRUE(),
MaxItem=8,[Net Sales]
MaxItem=24,[Total Total COST Of Goods Sold],
CALCULATE(SUM('Table'[ABS PTD ACTUAL]),
FILTER( 'Table', 'Table'[Report Name] =MAX('Table (2)'[IS Item]) ) ))

Note: In my test, I'm replacing them with numbers because of the lack of information about [Net Sales] and [Total Total COST Of Goods Sold]. Please provide more details if I understand incorrectly.

Best regards,

Mengmeng Li

[Anonymous]

Thanks for this.  

Basically, I have a template with an Income Statement layout (including subtotals and blank rows)

8 would be the index number for Net Sales.   

Why does the measure not work when I switch the hard coded value to be a measure (Net Sales). 

Here is the actual measure where the individual line items work but now the subtotals don't calculate correctly.

=VAR MaxItem=max('MASTER_IS_TEMPLATE'[Original Index])

Return

Switch(TRUE(),

MaxItem=8,[Net Sales],

MaxItem=24,[Total Total COST Of Goods Sold],

CALCULATE(SUM('Summarized GL Data'[Total Activity]),

FILTER( 'Summarized GL Data', 'Summarized GL Data'[Report Name] =MAX('MASTER_IS_TEMPLATE'[IS Item]) ) ))

[Anonymous]

Hi @Anonymous ,

What is the DAX of your measure? The problem might be caused by the row context in the measure. It would be better to provide a link to the pbix file, which could help us with this issue.

Best regards,

Mengmeng Li