Forum Discussion
Help with Switch Measure and Subtotals
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) |
4 Replies
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
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]) ) ))
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