Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbreneman
Helper II
Helper II

Help with creating a measure based on two columns in visualization

Hello!

 

I have the table visualization shown in the screenshot below (icon column was filled in manually to represent end goal). Actual is a "Count (Distinct)" from another table and is showing value as "Percent of grand total". Budget is a measure (also shown in screenshot).

PayerBudgetsTableVisualization.png

BudgetMeasure.png

PercentofGrandTotal.png

 

My end goal is to use conditional formatting and icons to have a red or green symbol on each row to show whether or not actual is greater than or equal to budget. I realize I need to use a measure to accomplish this. I created a measure called Icon but I can't seem to figure out how to use DAX to come up with the same thing that "Percent of grand total" is doing.

 

So far I have this, but I know it's not correct as the variable is giving the distinct count but not the percentage of grand total:

Icon = 
VAR
    Actual = DISTINCTCOUNT(view_ods_patient_census_rate_actual[BedID])
RETURN
    IF(Actual >= [DisplayBudget],"1","0")

Thanks in advance for any help you can provide!

1 ACCEPTED SOLUTION
DataZoe
Employee
Employee

Hi @rbreneman ,

 

From what you described it seems you need to write the measure that will do the % of total. 

Actual =
DIVIDE (
    DISTINCTCOUNT ( view_ods_patient_census_rate_actual[BedID] ),
    CALCULATE (
        DISTINCTCOUNT ( view_ods_patient_census_rate_actual[BedID] ),
        REMOVEFILTERS ( Table[Category] )
    )
)

Then you should be able to do your icon measure like you were trying.

Icon =
IF ( [Actual] >= [DisplayBudget], "1""0" )

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @rbreneman ,

 

Try this measure.

Measure = IF(MAX('Table'[Acutal])>MAX('Table'[Budget]),"SignLow","CircleHigh")

 

To show icons based on cell values, select Conditional formatting for the field, and then select Format by Field value. Select based on Measure field.

20.png

21.png

 

You can check more details from here.

 

 

There is a list of icon names at the bottom of this article:

https://powerbiuniversity.com/actualizacion-julio-fin-los-iconos-formato-condicional-la-creacion-kpi...

 

I post the list here for your reference:

 

IdName
1SignLow
2SignMedium
3CircleHigh
4CircleMedium
5CircleLow
64CircleLow
7CircleMedium1
84CircleMedium2
9CircleEmpty
10Circle25
11CircleHalf
12Circle75
13CircleFilled
14TrafficLow
15TrafficMedium
16TrafficHigh
17TrafficBlackRimmed
18TrafficLowLight
19TrafficMediumLight
20TrafficHighLight
21CircleHalf
22TrafficBlackRimmedLight
23ColoredArrowDownRight
24ColoredArrowRight
25ColoredArrowUpRight
26ColoredArrowUp
27GreyArrowDown
28GreyArrowDownRight
29GreyArrowRight
30GreyArrowUpRight
31GreyArrowUp
32FlagLow
33FlagMedium
34FlagHigh
35FlagBlack
36SymbolLow
37SymbolMedium
38SymbolHigh
39CircleSymbolLow
40CircleSymbolMedium
41CircleSymbolHigh
42StarLow
43StarMedium
44StarHigh
45StarMediumLight
46StarHighLight
47TriangleLow
48TriangleMedium
49TriangleHigh
50SignalBarEmpty
51SignalBarLow
52SignalBarMedium
53SignalBarMedium2
54SignalBarFull
55SignalBarLowColored
56SignalBarMediumColored
57SignalBarMedium2Colored
58SignalBarFullColored
59QuadrantEmpty
60Quadrant25
61Quadrant50
62Quadrant75
63QuadrantFull
64Quadrant25Colored
65Quadrant50Colored
66Quadrant75Colored
67

QuadrantFullColored

 

22.png23.png24.png

 

 

 

Best Regards,

Stephen Tao

 

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

DataZoe
Employee
Employee

Hi @rbreneman ,

 

From what you described it seems you need to write the measure that will do the % of total. 

Actual =
DIVIDE (
    DISTINCTCOUNT ( view_ods_patient_census_rate_actual[BedID] ),
    CALCULATE (
        DISTINCTCOUNT ( view_ods_patient_census_rate_actual[BedID] ),
        REMOVEFILTERS ( Table[Category] )
    )
)

Then you should be able to do your icon measure like you were trying.

Icon =
IF ( [Actual] >= [DisplayBudget], "1""0" )

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Thanks! Sorry, forgot to come back and mark accepted, but this worked perfectly! Thanks again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.