Creating Visual Categories based on Calculated Measure

[willit6182]

I would love some help from you all re making legend categories for a visual from a calculated measure.

Below is representative data that I have in a single table:

Encounter	Code	RVU	Month
1	49083	2	Jan
2	36014	3.02	Jan
2	36015	3.51	Jan
2	37211	0	Jan
2	76937	0.3	Jan
3	49452	2.86	Feb
4	10160	1.25	Feb
4	77012	1.5	Feb
5	47534	7.6	March
6	93922	0.25	March
7	36589	2.28	March
7	36901	3.36	March
8	49083	2	March
9	10005	1.46	March
10	38222	1.44	March
10	77012	1.5	March

As you can see, multiple Billing Codes and associated RVU Values for each Encounter. I am trying to make a stacked column chart visual:

Y- axis = Count of Encounter (Distinct)

X- axis = grouped by Month

Legend = "Sum RVU for Encounter > 5" OR "Sum RVU for Encounter < 5"

         (i.e. Encounter 1 = 2.0 RVU; Encounter 2 = 6.83 RVU; etc)

I have correctly classified individual Encounters as ">5" (High) or "<5" (Low) in a table visual as below using the following measure...

Total RVU Classification :=

VAR TotalRVU = SUMX(

VALUES('Table'[Encounter]),

CALCULATE(

SUM('Table'[RVU])

)

)

RETURN

IF(TotalRVU > 5, "High", "Low")

HOWEVER, I am not able to drag this measure to the legend field in my bar chart as I thought I could so that I can get stacked columns delineated by the 2 categories. Can anyone shed light on this? Do I need to be using a different measure or data format to allow it to be put into the legend field?

Thanks!!

[Irwan · Accepted solution]

hello @willit6182 

I believe you can only add column as visual legend.

So if you have to add legend, you need to make another calculated column which defines Total RVU Classification.

Here is one of many ways to do this.

1. create new table and summarize your original table. In summarize table, calculate sum of RVU as well.

Summarize =

SUMMARIZE(

    'Table',

    'Table'[Encounter],

    'Table'[Month],

    "Sum RVU per Encounter",

    CALCULATE(SUM('Table'[RVU]),FILTER(ALL('Table'),'Table'[Encounter]=EARLIER('Table'[Encounter])))

)

2. create a calculated column to define Total RVU Classification.

Total RVU Classification =

IF(

    'Summarize'[Sum RVU per Encounter]>5,

    "High",

    "Low"

)

3. make relationship between original table and summarized table.

4. plot your data in stacked column chart. Take Total RVU Classification column as visual legend.

Also dont forget to change Y-Axis value into count distinct since you want to show encounter value as distinct value.

Now, let us check using your original table. i.e in March, the red color indicates sum of RVU that will get High in Total RVU Classification. While the green color indicates sum of RVU that will get Low in Total RVU Classification.

and in stacked visual in March, value of High is 2 (distinct count encounter red color) and value of Low is 4 (distinct count encounter green color).

Hope this will help you.

Thank you.

[v-kaiyue-msft · Accepted solution]

Hi @willit6182 ,

Thanks for the reply from @Irwan , please allow me to provide another insight:

In Power BI, a Measure is usually not used directly as a legend field. Legend fields usually require a Dimension, which is categorical data, whereas Measures are numeric data that are used for calculations and aggregation.

You can put it into the legend by creating calculated columns.

Total RVU Classification =
VAR TotalRVU =
    CALCULATE (
        SUM ( 'Table'[RVU] ),
        FILTER ( 'Table', 'Table'[Encounter] = EARLIER ( 'Table'[Encounter] ) )
    )
RETURN
    IF ( TotalRVU > 5, "High", "Low" )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwan · Accepted solution]

hello @willit6182 

I believe you can only add column as visual legend.

So if you have to add legend, you need to make another calculated column which defines Total RVU Classification.

Here is one of many ways to do this.

1. create new table and summarize your original table. In summarize table, calculate sum of RVU as well.

Summarize =

SUMMARIZE(

    'Table',

    'Table'[Encounter],

    'Table'[Month],

    "Sum RVU per Encounter",

    CALCULATE(SUM('Table'[RVU]),FILTER(ALL('Table'),'Table'[Encounter]=EARLIER('Table'[Encounter])))

)

2. create a calculated column to define Total RVU Classification.

Total RVU Classification =

IF(

    'Summarize'[Sum RVU per Encounter]>5,

    "High",

    "Low"

)

3. make relationship between original table and summarized table.

4. plot your data in stacked column chart. Take Total RVU Classification column as visual legend.

Also dont forget to change Y-Axis value into count distinct since you want to show encounter value as distinct value.

Now, let us check using your original table. i.e in March, the red color indicates sum of RVU that will get High in Total RVU Classification. While the green color indicates sum of RVU that will get Low in Total RVU Classification.

and in stacked visual in March, value of High is 2 (distinct count encounter red color) and value of Low is 4 (distinct count encounter green color).

Hope this will help you.

Thank you.