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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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
2 years ago

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