Forum Discussion

willit6182's avatar
willit6182
Frequent Visitor
2 years ago

Formula help to sum groups within a large table based on filters

Hello everyone,

 

I'm having trouble coming up with the correct DAX expression for a measure I need.

 

My data is only available in one large table.

 

Encounter IDs are unique to patient visits (a concatenated acct number & date of service). For each Encounter ID there are multiple Procedure Codes charged, and each Procedure code has an RVU number attached to it. Therefore, there are multiple instances of any one Encounter ID in the table.

 

I am trying to write a measure that basically does the following:

 

For each DISTINCT Encounter ID, Adding all of the RVU values that were Charged, Adding those values together for a complete total, and then classifying that Encounter as Low RVU (<5 total) or High RVU (> 5 total).

 

Putting it another way, I want to 

1. Filter the table using the first Encounter ID and a [Record Type Desc] = "Charge"

2. Add the RVU value of all rows of the filtered table to come up with a SUM of RVU for that specific Encounter ID

3. Iterating this process for each Distinct Encounter ID

4. then adding the SUM of RVU for each Encounter ID together for an overall total RVU value

then

5. classifying each Encounter ID as "High" (Sum of Encoutner RVU >5) or "Low" (Sum of Encounter RVU <5)

 

I have tried many combinations of CALCULATE, SUM, SUMX and FILTER but cannot seem to get it to work and can't find any previous article that I can correlate directly. Any help would be greatly appreciated!!

 

Encounter IDDate Svc FromCredited Prov NameAcct NbrProcedure CodeRVUAmount ChargeRecord Type DescSITE
1013943452991/8/2024Bose1013943325552.27 Payment AssignmentPBR
1013943452991/8/2024Bose1013943325552.27 Payment AssignmentPBR
1013943452991/8/2024Bose1013943325552.27$339.00ChargePBR
1013943452991/8/2024Bose1013943325552.27 AdjustmentPBR
1013943452991/8/2024Bose1013943325552.27 AdjustmentPBR
1129962452991/8/2024Bose1129962365892.28 AdjustmentPBR
1129962452991/8/2024Bose1129962365892.28$418.00ChargePBR
1129962452991/8/2024Bose1129962365892.28 Payment AssignmentPBR
1129962452991/8/2024Bose1129962365892.28 AdjustmentPBR
1129962452991/8/2024Bose1129962365892.28 AdjustmentPBR
1204089452991/8/2024Bose1204089325552.27$339.00ChargePBR
1204089452991/8/2024Bose1204089325552.27 Payment AssignmentPBR
1204089452991/8/2024Bose1204089325552.27 AdjustmentPBR
1204089452991/8/2024Bose1204089325552.27 AdjustmentPBR
1209124452991/8/2024Bose1209124490832.00$324.00ChargePBR
1209124452991/8/2024Bose1209124490832.00 AdjustmentPBR
1209124452991/8/2024Bose1209124490832.00 Payment AssignmentPBR
1216239452991/8/2024Bose1216239325552.27 Payment AssignmentPBR
1216239452991/8/2024Bose1216239325552.27$339.00ChargePBR
1250734452991/8/2024Bose1250734365615.79 Payment AssignmentPBR
1250734452991/8/2024Bose1250734365615.79 AdjustmentPBR
1250734452991/8/2024Bose1250734365615.79$1,056.00ChargePBR
1250734452991/8/2024Bose125073476937,260.30 Payment AssignmentPBR
1250734452991/8/2024Bose125073476937,260.30 AdjustmentPBR
1250734452991/8/2024Bose125073476937,260.30$47.00ChargePBR
1250734452991/8/2024Bose125073477001,260.38 Payment AssignmentPBR
1250734452991/8/2024Bose125073477001,260.38$60.00ChargePBR
1250734452991/8/2024Bose125073477001,260.38 AdjustmentPBR
22001452991/8/2024Bose2200193922,260.25$39.00ChargePBR
22001452991/8/2024Bose2200193922,260.25 Payment AssignmentPBR
22001452991/8/2024Bose2200193922,260.25 AdjustmentPBR
410318452991/8/2024Bose410318490832.00 AdjustmentPBR
410318452991/8/2024Bose410318490832.00 AdjustmentPBR
410318452991/8/2024Bose410318490832.00$324.00ChargePBR
410318452991/8/2024Bose410318490832.00 AdjustmentPBR

2 Replies

  • willit6182 , Try measures like

     

    Sum RVU per Encounter =
    SUMX(
    VALUES(Table[Encounter ID]),
    CALCULATE(
    SUM(Table[RVU]),
    Table[Record Type Desc] = "Charge"
    )
    )


    RVU Classification =
    SUMX(
    VALUES(Table[Encounter ID]),
    VAR TotalRVU = CALCULATE(
    SUM(Table[RVU]),
    Table[Record Type Desc] = "Charge"
    )
    RETURN
    IF(TotalRVU > 5, "High", "Low")
    )

     

     

    Total RVU Classification =
    VAR TotalRVU = SUMX(
    VALUES(Table[Encounter ID]),
    CALCULATE(
    SUM(Table[RVU]),
    Table[Record Type Desc] = "Charge"
    )
    )
    RETURN
    IF(TotalRVU > 5, "High", "Low")

    • willit6182's avatar
      willit6182
      Frequent Visitor

      Thanks!

      I got Measures to work using the 1st and 3rd code sets. For some reason I kept getting a syntax error with the 2nd, but I think the 3rd accomplishes this for me.

       

      My next question is, how to I create a measure to slice data based on this categorization. In another situation, I was able to drag a pre-existing table column over to the legend field of a bar chart and have the stacked charts split by category. However, in this case, the Measure based off the 3rd code set won't populate the Legend field.  Basically, I want a stacked bar chart split between with separate "high" and "low" partitions when the whole bar is the total of both.

       

      Here's what i'm trying to replicate with the new measure...

       

      chart trying to replicatechart trying to replicate