P&L - Paths

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 Thanks for your help with this! What worked was doing the following

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?