P&L - Paths

[MStark]

Hi,

Im working on creating a P&L in BI but need some help. Started working with Paths but feel free to advise if you think another method will work better

Have Paths, Data and some sort table. 

Basically want the amount of the different levels of data with some subtotals (ex Total R&B Revenue, EBITDAR) and PPD which is:
if level 1 = Census, amount divided by days in month
if level 1  = Revenue and level 2 is revenue Current, amount divided by amount of matching level 2 in census 
all others, amount divided by full census

Path = PATH('Path'[Child],'Path'[Parent])

Level 1 = PATHITEM('Path'[Path],1)

not working fully but started with - Custom Amount2 =

VAR Level1 = SELECTEDVALUE('Path'[Level 1])

VAR Level2 = SELECTEDVALUE('Path'[Level 2])

VAR OperatingDeps = {

    "Nursing and Medical",

    "Social Services",

    "Therapy and Ancillary",

    "Recreation",

    "Food and Nutrition"}

Var Census=CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]))

Var RB=CALCULATE(SUM(Data[Amount]),TREATAS({"Revenue Current","Revenue Adjustments"},'Path'[Level 2]))

Var Rev=CALCULATE(SUM(Data[Amount]),TREATAS({"Revenue"},'Path'[Level 1]))

Var OperatingExp=CALCULATE(SUM(Data[Amount]),TREATAS(OperatingDeps,'Path'[Level 2]),REMOVEFILTERS('Path'))

VAR ManagementExp=CALCULATE(SUM(Data[Amount]),TREATAS({"Management Expenses"},'Path'[Level 1]),REMOVEFILTERS('Path'))

RETURN

SWITCH(    TRUE(),    Level2="Total R&B Revenue",RB,

    Level1="EBITDAR", Rev+OperatingExp+ManagementExp,

    SUM(Data[Amount]))

Sample outcome in Excel:

Level 1	Level 2	Level 3	Level 4	Level 5	Level 6		Amount	PPD	PPD Calc
Census	Medicaid						2,041.00	72.89	amount divided by days in month
Census	Medicare						184.00	6.57	amount divided by days in month
Census	Private						131.00	4.68	amount divided by days in month
Revenue	Revenue Current	Medicaid					764,444.00	374.54	amount divided by amount in matching level 2 in census
Revenue	Revenue Current	Medicare					160,000.00	869.57	amount divided by amount in matching level 2 in census
Revenue	Revenue Current	Private					50,000.00	381.68	amount divided by amount in matching level 2 in census
Revenue	Revenue Adjustments	Medicaid					6,000.00	2.55	amount divided by full cenus
Revenue	Revenue Adjustments	Medicare					(400.00)	0.17	amount divided by full cenus
Revenue	Revenue Adjustments	Private					400.00	0.17	amount divided by full cenus
Revenue	Total R&B Revenue						980,444.00	416.15	amount divided by full cenus
Revenue	Other Revenue	Revenue>Sequester					(2,600.00)	1.10	amount divided by full cenus
Revenue		Revenue>Miscellaneous					400.00	0.17	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	CNA	Nursing Wages >CNA>Regular	(95,000.00)	40.32	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	CNA	Nursing Wages >CNA>Regular	(80,000.00)	33.96	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	CNA	Nursing Wages >CNA>OT		(2,000.00)	0.85	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	CNA	Nursing Wages >CNA>OT		(1,000.00)	0.42	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	LPN	Nursing Wages >LPN>Regular	(60,000.00)	25.47	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	LPN	Nursing Wages >LPN>Regular	(55,000.00)	23.34	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	LPN	Nursing Wages >LPN>OT		(2,200.00)	0.93	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	LPN	Nursing Wages >LPN>OT		(880.00)	0.37	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	Restoritive Aide	Nursing Wages >Restorative Aide>Regular	(4,000.00)	1.70	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	Restoritive Aide	Nursing Wages >Restorative Aide>Regular	(3,000.00)	1.27	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	DC	Restoritive Aide	Nursing Wages >Restorative Aide>OT	(385.00)	0.16	amount divided by full cenus
Expense	Nursing and Medical	Nursing Wages	NDC	ADON	Nursing Wages >ADON>Regular	(3,000.00)	1.27	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	PR Dental Ins				(700.00)	0.30	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	PR Dental Ins				(700.00)	0.30	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	PR WC Ins				(4,600.00)	1.95	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	PR WC Ins				(2,700.00)	1.15	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	PR WC Ins				(7,000.00)	2.97	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	Supplies				(18.00)	0.01	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	Supplies				(50.00)	0.02	amount divided by full cenus
Expense	Nursing and Medical	Nursing Other	Supplies				(80.00)	0.03	amount divided by full cenus
Expense	Social Services	Social Services Wages	Social Service Wages >Social Services Director>Regular			(3,700.00)	1.57	amount divided by full cenus
Expense	Social Services	Social Services Wages	Social Service Wages >Social Services Director>Regular			(3,700.00)	1.57	amount divided by full cenus
Expense	Social Services	Social Services Wages	Social Service Wages >Social Services Director>OT			(70.00)	0.03	amount divided by full cenus
Expense	Social Services	Social Services Wages	Social Service Wages >Social Services Director>OT			(60.00)	0.03	amount divided by full cenus
Expense	Social Services	Social Services Wages	Social Service Wages >Social Services Assistant>Regular			(1,500.00)	0.64	amount divided by full cenus
Expense	Social Services	Social Services Wages	Social Service Wages >Social Services Assistant>Regular			(1,900.00)	0.81	amount divided by full cenus
Expense	Social Services	Social Services Other	Vision Insurance				(1.00)
Expense	Social Services	Social Services Other	Vision Insurance				(1.00)
Expense	Social Services	Social Services Other	PR Dental Ins				(16.00)
Expense	Social Services	Social Services Other	PR Dental Ins				(15.00)
Expense	Social Services	Social Services Other	PR Health Ins				(76.00)
Expense	Social Services	Social Services Other	PR Health Ins				(1,750.00)
Expense	Social Services	Social Services Other	PR Taxes				(480.00)
Expense	Social Services	Social Services Other	PR Taxes				(490.00)
Expense	Social Services	Social Services Other	PR WC Ins				(110.00)
Expense	Social Services	Social Services Other	PR WC Ins				(60.00)
Expense	Social Services	Social Services Other	PR WC Ins				(170.00)

[v-achippa · Accepted solution]

Hi @MStark,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for sharing all the details. Here are the steps to build your P&L with correct subtotals and PPD logic. Please follow below steps:

• Create calculated columns for hierarchy levels like below:
  

  Level 1 = PATHITEM('Path'[Path], 1, TEXT)

  Level 2 = PATHITEM('Path'[Path], 2, TEXT)

  Level 3 = PATHITEM('Path'[Path], 3, TEXT)

  Level 4 = PATHITEM('Path'[Path], 4, TEXT)

  Level 5 = PATHITEM('Path'[Path], 5, TEXT)

  Level 6 = PATHITEM('Path'[Path], 6, TEXT)

• Create measure for Total Census like below:
  

  TotalCensus = CALCULATE(SUM('Data'[Amount]), 'Path'[Level 1] = "Census")

• Create measure for Matching Census like below:
  

  MatchingCensus = VAR ThisLevel2 = SELECTEDVALUE('Path'[Level 2])

  RETURN

  CALCULATE(

      SUM('Data'[Amount]),

      'Path'[Level 1] = "Census",

      'Path'[Level 2] = ThisLevel2

  )

• Create Custom Amount measure for subtotals:
  

  Custom Amount =

  VAR Level1 = SELECTEDVALUE('Path'[Level 1])

  VAR Level2 = SELECTEDVALUE('Path'[Level 2])

  VAR Amount = SUM('Data'[Amount])

  VAR RB = CALCULATE(SUM('Data'[Amount]),
  'Path'[Level 2] IN { "Revenue Current", "Revenue Adjustments" }
  )

  VAR Rev = CALCULATE(SUM('Data'[Amount]), 'Path'[Level 1] = "Revenue")

  VAR OpDeps = {

      "Nursing and Medical",

      "Social Services",

      "Therapy and Ancillary",

      "Recreation",

      "Food and Nutrition"

  }

  VAR OperatingExp = CALCULATE(SUM('Data'[Amount]),
  'Path'[Level 2] IN OpDeps, REMOVEFILTERS('Path'))

  VAR MgmtExp = CALCULATE(SUM('Data'[Amount]),

      'Path'[Level 1] = "Management Expenses", REMOVEFILTERS('Path'))

  RETURN

      SWITCH(

          TRUE(),

          Level2 = "Total R&B Revenue", RB,

          Level1 = "EBITDAR", Rev + OperatingExp + MgmtExp,

          Amount

      )

• Create PPD measure like below, this calculates per patient day using the correct base depending on the row:
  

  PPD =

  VAR Level1 = SELECTEDVALUE('Path'[Level 1])

  VAR Level2 = SELECTEDVALUE('Path'[Level 2])

  VAR Amount = [Custom Amount]

  VAR TotalCensus = [TotalCensus]

  VAR MatchingCensus = [MatchingCensus]

  VAR DaysInMonth = 28  

  RETURN

      SWITCH(

          TRUE(),

          Level1 = "Census", DIVIDE(Amount, DaysInMonth),

          Level1 = "Revenue" && Level2 = "Revenue Current", DIVIDE(Amount, MatchingCensus),

          DIVIDE(Amount, TotalCensus)

      )

In the Matrix visual, add Level 1 to Level 6 as Rows, add Custom Amount and PPD as Values and use slicers for dates or any filters if needed.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa · Accepted solution]

Hi @MStark,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for sharing all the details. Here are the steps to build your P&L with correct subtotals and PPD logic. Please follow below steps:

• Create calculated columns for hierarchy levels like below:
  

  Level 1 = PATHITEM('Path'[Path], 1, TEXT)

  Level 2 = PATHITEM('Path'[Path], 2, TEXT)

  Level 3 = PATHITEM('Path'[Path], 3, TEXT)

  Level 4 = PATHITEM('Path'[Path], 4, TEXT)

  Level 5 = PATHITEM('Path'[Path], 5, TEXT)

  Level 6 = PATHITEM('Path'[Path], 6, TEXT)

• Create measure for Total Census like below:
  

  TotalCensus = CALCULATE(SUM('Data'[Amount]), 'Path'[Level 1] = "Census")

• Create measure for Matching Census like below:
  

  MatchingCensus = VAR ThisLevel2 = SELECTEDVALUE('Path'[Level 2])

  RETURN

  CALCULATE(

      SUM('Data'[Amount]),

      'Path'[Level 1] = "Census",

      'Path'[Level 2] = ThisLevel2

  )

• Create Custom Amount measure for subtotals:
  

  Custom Amount =

  VAR Level1 = SELECTEDVALUE('Path'[Level 1])

  VAR Level2 = SELECTEDVALUE('Path'[Level 2])

  VAR Amount = SUM('Data'[Amount])

  VAR RB = CALCULATE(SUM('Data'[Amount]),
  'Path'[Level 2] IN { "Revenue Current", "Revenue Adjustments" }
  )

  VAR Rev = CALCULATE(SUM('Data'[Amount]), 'Path'[Level 1] = "Revenue")

  VAR OpDeps = {

      "Nursing and Medical",

      "Social Services",

      "Therapy and Ancillary",

      "Recreation",

      "Food and Nutrition"

  }

  VAR OperatingExp = CALCULATE(SUM('Data'[Amount]),
  'Path'[Level 2] IN OpDeps, REMOVEFILTERS('Path'))

  VAR MgmtExp = CALCULATE(SUM('Data'[Amount]),

      'Path'[Level 1] = "Management Expenses", REMOVEFILTERS('Path'))

  RETURN

      SWITCH(

          TRUE(),

          Level2 = "Total R&B Revenue", RB,

          Level1 = "EBITDAR", Rev + OperatingExp + MgmtExp,

          Amount

      )

• Create PPD measure like below, this calculates per patient day using the correct base depending on the row:
  

  PPD =

  VAR Level1 = SELECTEDVALUE('Path'[Level 1])

  VAR Level2 = SELECTEDVALUE('Path'[Level 2])

  VAR Amount = [Custom Amount]

  VAR TotalCensus = [TotalCensus]

  VAR MatchingCensus = [MatchingCensus]

  VAR DaysInMonth = 28  

  RETURN

      SWITCH(

          TRUE(),

          Level1 = "Census", DIVIDE(Amount, DaysInMonth),

          Level1 = "Revenue" && Level2 = "Revenue Current", DIVIDE(Amount, MatchingCensus),

          DIVIDE(Amount, TotalCensus)

      )

In the Matrix visual, add Level 1 to Level 6 as Rows, add Custom Amount and PPD as Values and use slicers for dates or any filters if needed.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[MStark · Accepted solution]

@v-achippa Thanks for your help with this! What worked was doing the following

---Census = CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]),REMOVEFILTERS('Path'))

---DaysInMonth = DAY(EOMONTH(MAX('All Dates'[Date]), 0))

---MatchingCensus =

VAR SelectedLevel3 = LOWER(TRIM(SELECTEDVALUE('Path'[Level 3])))

RETURN

IF(    NOT ISBLANK(SelectedLevel3),    CALCULATE(SUM('Data'[Amount]), FILTER( ALL('Path'), 'Path'[Level 1] = "Census"   &&    LOWER(TRIM(SUBSTITUTE('Path'[Level 2], " Census", ""))) = SelectedLevel3  )))

 ---Path P&L AMNT =

VAR Level1 = SELECTEDVALUE('Path'[Level 1])

VAR Level2 = SELECTEDVALUE('Path'[Level 2])

VAR OperatingDeps = {

    "Nursing and Medical",

    "Social Services",

    "Therapy and Ancillary",

    "Recreation",

    "Food and Nutrition",}

Var Census=CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]))

Var RB=CALCULATE(SUM(Data[Amount]),TREATAS({"Revenue Current","Revenue Adjustments"},'Path'[Level 2]),REMOVEFILTERS('Path'))

Var Rev=CALCULATE(SUM(Data[Amount]),TREATAS({"Revenue"},'Path'[Level 1]),REMOVEFILTERS('Path'))

Var OperatingExp=CALCULATE(SUM(Data[Amount]),TREATAS(OperatingDeps,'Path'[Level 2]),REMOVEFILTERS('Path'))

VAR ManagementExp=CALCULATE(SUM(Data[Amount]),TREATAS({"Management Expenses"},'Path'[Level 1]),REMOVEFILTERS('Path'))

RETURN

SWITCH(

    TRUE(),

    Level2="Total R&B Revenue",RB,

    Level1="EBITDAR", Rev+OperatingExp+ManagementExp,

    SUM(Data[Amount]))

---Path P&L PPD =

VAR Level1 = SELECTEDVALUE('Path'[Level 1])

VAR Level2 = SELECTEDVALUE('Path'[Level 2])

VAR Amount = [Path P&L AMNT]

VAR TotalCensus = [Census]

VAR MatchingCensus = [MatchingCensus]

VAR DaysInMonth = [DaysInMonth]

RETURN

    SWITCH( TRUE(),

        Level1 = "Census",   DIVIDE(Amount, DaysInMonth),

        Level1 = "Revenue" && Level2 = "Revenue Current", DIVIDE(Amount, MatchingCensus),

        TRUE(), DIVIDE(Amount,TotalCensus ))

The issue is that since this is a matrix visual, the total column for Revenue Current is blank by PPD. Since this is not falling into any of the first 2 switch statements should generate amount / total census. Probably an issue since its not real data but a Total line... Any suggestions?

[MStark]

Actually figured it out. Updated the Matching Census to include a full census fallback

---MatchingCensus =

VAR SelectedLevel3 = LOWER(TRIM(SELECTEDVALUE('Path'[Level 3])))
VAR TotalCensus=[Census]

RETURN

IF(    NOT ISBLANK(SelectedLevel3),    CALCULATE(SUM('Data'[Amount]), FILTER( ALL('Path'), 'Path'[Level 1] = "Census"   &&    LOWER(TRIM(SUBSTITUTE('Path'[Level 2], " Census", ""))) = SelectedLevel3  )),TotalCensus)