Create Calculated Rows

[dospencer]

Hi,

I need to create a Net Tuition visual that must include calculated rows at 3 levels. The calculate rows are interspersed between rows from the main data table. I have included samples showing the main data table and what the output should look like. 

1. What visual would be best, Table or Matrix?
2. How would I achieve generating the calculated rows and inserting them in the correct places?

   I am new to PowerBI so I would appreciate as much detail as possible.

   Data Table:

   Student Level	Term	Transaction	Students Budgeted	Students Billed	Tuition Budgeted	Tuition Billed
   Undergraduate	Fall 2020	Undergraduate Tuition & Fees	909	114	$24,000	$26,000
   Undergraduate	Fall 2020	Room and Board			$23,000	$15,000
   Undergraduate	Fall 2020	Study Abroad	43	2	$7,500	$100
   Undergraduate	Fall 2020	Financial Aid			$12,000	$11,000
   Undergraduate	Spring 2021	Undergraduate Tuition & Fees	832	51	$17,000	$16,000
   Undergraduate	Spring 2021	Study Abroad	55	0	$8,000	$0
   Undergraduate	Spring 2021	Financial Aid			$9,000	$5,000
   Graduate	Fall 2020	Executive MBA	909	114	$24,000	$26,000
   Graduate	Fall 2020	Professional MBA	43	2	$7,500	$100
   Graduate	Fall 2020	Early Advantage MBA	43	2	$7,500	$100
   Graduate	Fall 2020	Financial Aid			$12,000	$11,000

Visual:

The rows with the text in blue are the calculated rows:

1. The Tuition Revenue is the sum of all the revenue items (every item except the Financial Aid).
2. Net Tuition is Tuition Revenue minus Financial Aid.
3. Percent Discount is calcualted as Financial Aid divided by Tuition Revenue

   Undergraduate	Students Budgeted	Students Billed	Tuition Budgeted	Tuition Billed
   Fall 2020
   Undergraduate Tuition & Fees	909	114	$24,000	$26,000
   Room and Board			$23,000	$15,000
   Study Abroad	43	2	$7,500	$100
   Tuition Revenue	952	116	$54,500	$41,100
   Financial Aid			$12,000	$11,000
   Net Tuition			$42,500	$30,100
   Percent Discount			22%	27%
   Spring 2021
   Undergraduate Tuition & Fees	832	51	$17,000	$16,000
   Study Abroad	55	0	$8,000	$0
   Tuition Revenue	887	51	$25,000	$16,000
   Financial Aid			$9,000	$5,000
   Net Tuition			$16,000	$11,000
   Percent Discount			36%	31%
   Undergraduate Tuition Revenue			$79,500	$57,100
   Financial Aid			$21,000	$16,000
   Net Tuition			$58,500	$41,100
   Percent Discount			26%	28%
   Graduate	Students Budgeted	Students Billed	Tuition Budgeted	Tuition Billed
   Fall 2020
   Executive MBA	909	114	$24,000	$26,000
   Professional MBA	43	2	$7,500	$100
   Early Advantage MBA	43	2	$7,500	$100
   Tuition Revenue	952	116	$39,000	$26,200
   Financial Aid			$12,000	$11,000
   Net Tuition			$27,000	$15,200
   Percent Discount			31%	42%
   Graduate Tuition Revenue			$64,000	$42,200
   Financial Aid			$21,000	$16,000
   Net Tuition			$43,000	$26,200
   Percent Discount			33%	38%
   College Tuition Revenue			$143,500	$99,300
   Financial Aid			$42,000	$32,000
   Net Tuition			$101,500	$67,300
   Percent Discount			29%	32%

[dospencer]

@DataInsights Thank you. I will go through to see how I can make it work with my data.

[dospencer]

@DataInsights I tried the Profitbase Financial Reporting Matrix but I didn't see it working for my use case. There was a grid option but it required that I add the row at specific places and it therefrore assumed that the row will always appear at the same row number. In my case, the row where Tuition Revenue, Net Tuition and Percent Discount will vary based on the number detail rows above it.

I tried adding Tuition Revenue, Net Tuition and Percent Discount in the source data set and then using a measure calculation to compute the Tuition Revenue where the Tuition Revenue row appears but I can't get that working either.

[DataInsights]

@dospencer,

Try the Profitbase Financial Reporting Matrix. It allows you to create custom subtotals, among other things.

https://appsource.microsoft.com/en-US/product/power-bi-visuals/WA200000642?tab=Overview