Matrix - Indenting row labels, financial statements, etc.

[mrothschild]

I fear I've complicated things much more than they need to be.  I think I'm quite close to getting to a desired output, but have wrapped myself in knots at this point.

After watching this video: https://www.youtube.com/watch?v=mUVz59xCRMI 

I'm essentially trying to recreate what is shown.  I have my data (shown below) pulled in from Excel and then is unpivoted so that my columns are: 

• Income Statement Item
• Sort Order
• Month
• Value
• Category

I built a Measure called [Financial Values] 

Financial Values = 
    SUM('Management Company Income Statement'[Value])

I've started to build Measures for each individual line item, e.g., Sales, with the following DAX formula: Sales

Sales = 
    IF(
        CALCULATE( [Financial Values],
            'Management Company Income Statement'[Income Statement Item] = "Sales") = 0, 
        BLANK(),
            CALCULATE( [Financial Values],
                    'Management Company Income Statement'[Income Statement Item] = "Sales")
            
    )

Neither of these matrix outputs shown below are ideal.  

The one on the left, has rows of "Income Statement Item", and Values of "Value" from the unpivoted data.

The one on the right has rows of "Income Statement Item" and Values of "Sales" and "Advertising & Marketing" 

At this point, I assume that the one on the right is probably the better, though more labor-intensive, way of the ultimate goal, assuming I can limit the columns to only dates and not individual income statement items, which will then facilitate using the SWITCH ( TRUE() )  methodology described in the video.  

But related, how do I indent these row labels?

Line Item	Q1	Q2
Sales	85000	50000
Total Income	85000	50000
Gross Profit	85000	50000
Advertising & Marketing	224	136
Bank Charges & Fees		124
Insurance - Health	6072	6072
Insurance - Professional and Other	249	282
IT Equipment and Services	15	262
Legal & Professional Services		1822
Meals & Entertainment - Client	31	61
Office Expenses
Office Supplies & Software		12
Taxes & Licenses
Taxes	2616	2319
Wages	47381	55662
Total Payroll Expenses	49999	57981
Travel	250	15
Travel Meals	14
Total Expenses	56853	66767
Net Operating Income	28147	-16767
Net Income	28147	-16767

[v-juanli-msft]

Hi @mrothschild 

Is the last table your example data?

The final output should be a table/matrix visual which shows the value of selected item(eg, sales", or "dvertising & Marketing") during several quarters, right?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrothschild]

Hi @v-juanli-msft, 

In the screenshot you edited, I am/was using Measures as opposed to Columns for the values, which I think caused the problem(s), but you're essentially correct, though I'd take out the entire header row that says "Sales/Advertising & Marketing" and only show the time period.

Thanks!

[v-juanli-msft · Accepted solution]

Hi @mrothschild 

Is your data like this:

Then your expected result is as below, right?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.