Data Bars

[sandwichnova]

Hi

I'm trying to put together some basic reports to make life easier. I'm fairly new to PowerBI so please forgive any stupid questions.

I'm trying to add data bars to a table with conditional formatting. But the bars are out of wack. I assume it's because the Maximum is set to the 'highest value'. Is there away to set this so that the maximum to Total Licenses?

[danextian · Accepted solution]

Hi @sandwichnova 

There  is currently no option to base the length of the databars on another measure. The row with the biggest value will always have the longest bar. Alternatively, you can use SVG images but you will have to setup it up for every measure that you want the SVG images to be based on. Pleasee attached sample pbix

Data Bar SVG = 
VAR _Value = [RandomValue] -- Current row value
VAR MaxValue = CALCULATE([RandomValue], ALLSELECTED()) -- Maximum value
VAR BarWidth = ROUND(DIVIDE(_Value, MaxValue) * 100, 0) -- Bar width as a percentage
VAR _Text = FORMAT(_Value, "$#,#") -- Text to display inside the bar
VAR BarColor = IF(NOT(HASONEVALUE('Table'[Value])), "RGBA(0,0,0,0)", "#81C784") -- Light green bar color but transparent at the total level
VAR TextColor = "black" -- Text color
VAR __Prefix = "data:image/svg+xml;utf8,"
VAR __Font = "Arial, sans-serif" -- Font family
VAR BarHeight = 12 -- Adjust the height of the bar
VAR BarYPosition = 5 -- Adjust the vertical starting point of the bar

RETURN
    __Prefix & "<svg xmlns='http://www.w3.org/2000/svg' width='100%' height='" & BarHeight + 5 & "'>
        <rect x='0' y='" & BarYPosition & "' width='" & BarWidth & "%' height='" & BarHeight & "' fill='" & BarColor & "' />
        <text x='50%' y='" & (BarYPosition + BarHeight / 2) & "' fill='" & TextColor & "' font-size='8' font-family='" & __Font & "' text-anchor='middle'>" & _Text & "</text>
    </svg>"

[Anonymous · Accepted solution]

Hi @sandwichnova ,

Provide another method.

First construct a table like this:

Then create a new measure:

Total Assigned Licenses = 
VAR __curr_product = SELECTEDVALUE('Table 2'[Product Title])
VAR __result = 
SWITCH(
    TRUE(),
    ISBLANK(__curr_product), CALCULATE(SUM('Table'[Assigned Licenses]), ALL()),
    __curr_product="Total", CALCULATE(SUM('Table'[Assigned Licenses]), ALL()),
    CALCULATE(SUM('Table'[Assigned Licenses]),'Table'[Product Title]=__curr_product)
)
RETURN
    __result

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[danextian · Accepted solution]

Hi @sandwichnova 

There  is currently no option to base the length of the databars on another measure. The row with the biggest value will always have the longest bar. Alternatively, you can use SVG images but you will have to setup it up for every measure that you want the SVG images to be based on. Pleasee attached sample pbix

Data Bar SVG = 
VAR _Value = [RandomValue] -- Current row value
VAR MaxValue = CALCULATE([RandomValue], ALLSELECTED()) -- Maximum value
VAR BarWidth = ROUND(DIVIDE(_Value, MaxValue) * 100, 0) -- Bar width as a percentage
VAR _Text = FORMAT(_Value, "$#,#") -- Text to display inside the bar
VAR BarColor = IF(NOT(HASONEVALUE('Table'[Value])), "RGBA(0,0,0,0)", "#81C784") -- Light green bar color but transparent at the total level
VAR TextColor = "black" -- Text color
VAR __Prefix = "data:image/svg+xml;utf8,"
VAR __Font = "Arial, sans-serif" -- Font family
VAR BarHeight = 12 -- Adjust the height of the bar
VAR BarYPosition = 5 -- Adjust the vertical starting point of the bar

RETURN
    __Prefix & "<svg xmlns='http://www.w3.org/2000/svg' width='100%' height='" & BarHeight + 5 & "'>
        <rect x='0' y='" & BarYPosition & "' width='" & BarWidth & "%' height='" & BarHeight & "' fill='" & BarColor & "' />
        <text x='50%' y='" & (BarYPosition + BarHeight / 2) & "' fill='" & TextColor & "' font-size='8' font-family='" & __Font & "' text-anchor='middle'>" & _Text & "</text>
    </svg>"