Proft and Loss Sum Totals

[cpadilla0024]

I have been testing for a little while and kind of hit a brick wall. I created a Profit and Loss matrix that need to be filterable for my users. For example, the Gross Patient Revenue needs to be Inpatient Revenue + Outpatient Revenue. I tried a couple different methods.

Method #1: I followed the lead of this method:

and here would be my sample measure:
MTD Actual = SWITCH(TRUE(),
MAX('PL Format'[Order ID]) =3, CALCULATE([MTD Actual Amt], FILTER(ALL('PL Format'), 'PL Format'[Order ID] in {1,2})), //Gross Patient Revenue
[MTD Actual Amt]
)

The problem is that I cannot filter Divisions or Departments off of this. It only works as an overall summary. I tried doing some filtering another way with no luck.

Method #2: I tried approaching the formula a different way. Summing the applicable Line Descriptions with a measure like this:
MTD Actual1 = SWITCH(TRUE(),
MAX('PL Format'[Order ID]) =3, CALCULATE([MTD Actual Amt], Detail[Line Description] = "Inpatient Revenue"), //Gross Patient Revenue
[MTD Actual Amt]
)

I am just stuck at this point. MTD Actual Amt is a measure into the Detail Table (the extracted data). PL Format is in reference to the video to get the layout of the P&L format.

 

[cpadilla0024]

I was able to solve this on my own. Thanks!

 

[Logit]

Could you provide the solution for others to learn from ? Thanks

 

[cpadilla0024]

If you follow the steps from the video I posted, the blank row labels are causing an issue when trying to sum with the filters. So, in the "helper" measure you need to force a blank there before using it in the master measure. Something like this:

MTD Actual Amt = SWITCH(TRUE(),
MAX('PL Format'[Order ID]) =4, "",
MAX('PL Format'[Order ID]) =8, "",
MAX('PL Format'[Order ID]) =13, "",
MAX('PL Format'[Order ID]) =15, "",
MAX('PL Format'[Order ID]) =17, "",
MAX('PL Format'[Order ID]) =28, "",
MAX('PL Format'[Order ID]) =30, "",
MAX('PL Format'[Order ID]) =32, "",
MAX('PL Format'[Order ID]) =34, "",
MAX('PL Format'[Order ID]) =36, "",
MAX('PL Format'[Order ID]) =38, "",
MAX('PL Format'[Order ID]) =40, "",
SUM(Detail[Actual]))