Clustered column chart with unpivoted data

[mrothschild]

I'm importing financial statement data from Quickbooks into Excel and then into PowerBI.  The data comes pivoted in Excel and then I unpivot in PowerBI to analyze it, so the basic form of the unpivot is a column of "Month" and a column of "Value", though the line item descriptors (e.g., sales, wages, travel expenses) and broad categories (e.g., revenue, expense, profit) remain as separate columns.  When I try to chart the data, it's summing everything, including revenues and expenses, and double-counting the sub-total lines.  

Any suggestions on how to create measures or calculated columns to visualize this data without this double-counting issue?

Example date is below:

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

[ImkeF]

Hi @mrothschild ,

if you're using data with aggregations already, you cannot use the Matrix-visual, as it will create additional aggregations.

So you can filter out the subtotals first and then perform aggregations on the line items instead.

If you just want to display the data and don't need an expandable hierarchy, you can use a table visual instead with your full data.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[mrothschild]

@Ashish_Mathur 

This link shows the essence of what I'm trying to accomplish: https://www.quora.com/How-do-I-combine-bar-graphs-of-stacked-and-unstacked-columns-in-single-chart-i...

And this one has the step-by-step in Excel: https://peltiertech.com/clustered-stacked-column-bar-charts/

From the data in my original post, I'd like to show, as an example, a stacked bar chart that looks, illustratively like the following:

                     [Insurance]t-1               [Insurance]t

                     [Bank Fees]t-1              [Bank Fees]t

                     [A&M]t-1                      [A&M]t

                     3Q18                             3Q19

but where the time periods can be drilled down and the prior period will change dynamically so that I can compare versus prior month, quarter, year, or budgeted forecast, based on a slicer.