Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm working on adding Bullet Chart Sparklines to a table that I've created. I'm following the article below:
https://www.csgpro.com/blog/2018/08/bullet-chart-sparklines-in-power-bi
The Bullet Charts will not render in the table I just get an "Image" icon in the table. The DAX is the same as in the example and I'm using Power BI Desktop September 2018.
It doesn't look like I can upload the file since it's my first post.
Here's the DAX:
Bullet Chart =
VAR vBaseText =
"data:image/svg+xml;utf8, _svg width=""100"" height=""100"" version=""1.1"" xmlns=""http://www.w3.org/2000/svg"" style=""background: %23ffffff""_
_rect x=""0"" y=""25"" rx=""2"" ry=""2"" width=""100"" height=""50"" style=""fill:%23f2f2f2;stroke-width:0;fill-opacity:1"" /_
_rect x=""0"" y=""45"" rx=""2"" ry=""2"" width=""#Actual"" height=""10"" style=""fill:%23333333;stroke-width:0;fill-opacity:1"" /_
_rect x=""#Goal"" y=""30"" rx=""2"" ry=""2"" width=""6"" height=""40"" style=""fill:%23888888;stroke:black;stroke-width:0;fill-opacity:1;stroke-opacity:1"" /_
_/svg_"
VAR vObjects = ALL( Sales[Location], Sales[City],Sales[Country],Sales[State/Province] )
VAR vMaxActual = MAXX( vObjects, [Total Sales] )
VAR vMaxGoal = MAXX( vObjects, [Total Goal] )
VAR vXAxisRangeBase = MAX( vMaxActual, vMaxGoal )
VAR vActual = INT( DIVIDE( [Total Sales], vXAxisRangeBase ) * 90 )
VAR vGoal = INT( DIVIDE( [Total Goal], vXAxisRangeBase ) * 90 )
VAR vReturn = SUBSTITUTE( SUBSTITUTE( vBaseText, "#Actual", vActual ), "#Goal", vGoal )
RETURN IF( [Total Sales], vReturn, BLANK() )
Here's the results that I'm getting in the table.
Solved! Go to Solution.
@Anonymous
Ended up going with a Bullet Chart but the pattern was similar. Here's a screen shot of the final table and the DAX for the Bullet Chart in the table.
BH vs AH =
// Static column color - use %23 instead of # for Firefox compatibility
VAR vBackground = "%23ffffff"
//Set Bar Color based on AH vs BH
VAR vBarColor = Switch(True(),
[AH] <= [BH], "%2339a964",
[BH] = 0, "%23ffa160",
[AH] > [BH], "%23cd1319")
VAR vActBarColor = "%23333333"
VAR vTargetBarColor = "%23888888"
// Base Text for drawing SVG Bullet Chart
VAR vBaseText =
"data:image/svg+xml;utf8, <svg width='100' height='100' version='1.1' xmlns='http://www.w3.org/2000/svg' style= 'background: " & vBackground & "'>
<rect x='0' y='25' rx='2' ry='2' width='100' height='50' style='fill:" & vBarColor & ";stroke-width:0;fill-opacity:1' />
<rect x='0' y='45' rx='2' ry='2' width=""#Actual"" height='10' style='fill:" & vActBarColor & ";stroke-width:0;fill-opacity:1' />
<rect x=""#Budget"" y='30' rx='2' ry='2' width='6' height='40' style='fill:" & vTargetBarColor & ";stroke:black;stroke-width:0;fill-opacity:1;stroke-opacity:1' />
</svg>"
//Define the X Axis Range
//List All Cost Codes
VAR vObjects = All(Hours[Cost Code])
//Find Max AH & BH Across Cost Codes
VAR vMaxActual = MAXX( vObjects, [AH] )
VAR vMaxBudget = MAXX( vObjects, [BH] )
VAR vXAxisRangeBase = MAX( vMaxActual, vMaxBudget )
//Define Distance Across X Axis as Percentage. Multiplying by 90 to Add Padding
VAR vActual = INT( DIVIDE( [AH], vXAxisRangeBase ) * 90 )
VAR vBudget = INT( DIVIDE( [BH], vXAxisRangeBase ) * 90 )
//Replace varibles with values in SVG Text
VAR vReturn = SUBSTITUTE( SUBSTITUTE( vBaseText, "#Actual", vActual ), "#Budget", vBudget )
//Check to see if there were some Actual Hours
RETURN IF( [AH], vReturn, BLANK() )
Hope this helps!!!
same issue( Have you found the solution?!
@Anonymous
Ended up going with a Bullet Chart but the pattern was similar. Here's a screen shot of the final table and the DAX for the Bullet Chart in the table.
BH vs AH =
// Static column color - use %23 instead of # for Firefox compatibility
VAR vBackground = "%23ffffff"
//Set Bar Color based on AH vs BH
VAR vBarColor = Switch(True(),
[AH] <= [BH], "%2339a964",
[BH] = 0, "%23ffa160",
[AH] > [BH], "%23cd1319")
VAR vActBarColor = "%23333333"
VAR vTargetBarColor = "%23888888"
// Base Text for drawing SVG Bullet Chart
VAR vBaseText =
"data:image/svg+xml;utf8, <svg width='100' height='100' version='1.1' xmlns='http://www.w3.org/2000/svg' style= 'background: " & vBackground & "'>
<rect x='0' y='25' rx='2' ry='2' width='100' height='50' style='fill:" & vBarColor & ";stroke-width:0;fill-opacity:1' />
<rect x='0' y='45' rx='2' ry='2' width=""#Actual"" height='10' style='fill:" & vActBarColor & ";stroke-width:0;fill-opacity:1' />
<rect x=""#Budget"" y='30' rx='2' ry='2' width='6' height='40' style='fill:" & vTargetBarColor & ";stroke:black;stroke-width:0;fill-opacity:1;stroke-opacity:1' />
</svg>"
//Define the X Axis Range
//List All Cost Codes
VAR vObjects = All(Hours[Cost Code])
//Find Max AH & BH Across Cost Codes
VAR vMaxActual = MAXX( vObjects, [AH] )
VAR vMaxBudget = MAXX( vObjects, [BH] )
VAR vXAxisRangeBase = MAX( vMaxActual, vMaxBudget )
//Define Distance Across X Axis as Percentage. Multiplying by 90 to Add Padding
VAR vActual = INT( DIVIDE( [AH], vXAxisRangeBase ) * 90 )
VAR vBudget = INT( DIVIDE( [BH], vXAxisRangeBase ) * 90 )
//Replace varibles with values in SVG Text
VAR vReturn = SUBSTITUTE( SUBSTITUTE( vBaseText, "#Actual", vActual ), "#Budget", vBudget )
//Check to see if there were some Actual Hours
RETURN IF( [AH], vReturn, BLANK() )
Hope this helps!!!
thanks a lot for a quick answer!!!
could you please help a bit more... I still cannot get the logic here.
so I'm openning
<svg
just after this part "data:image/svg+xml;utf8," and close it at the end, right? why do we have
" & vBackground & "'>
? what are we closing here?
@Anonymous
Not 100% sure on this.... I think that these are the initail parameters for the SVG. It needs to know what the Background color is so I'm passing in a variable "vBackground" and then closing off the opening bracket. Here's a snippet of that section in VS Code which may make it clearer.
thank you a lot, you really helped!!!!!! I think, you have to accept your answer as the acepted solution here to help others
Hello,
I'm working on adding a Bullet Chart to my table and it is not rendering. All I see is an "Image" Icon in the table:
Here's the DAX I'm using for the Bullet Chart:
Bullet Chart =
VAR vBaseText =
"data:image/svg+xml;utf8, _svg width=""100"" height=""100"" version=""1.1"" xmlns=""http://www.w3.org/2000/svg"" style=""background: %23ffffff""_
_rect x=""0"" y=""25"" rx=""2"" ry=""2"" width=""100"" height=""50"" style=""fill:%23f2f2f2;stroke-width:0;fill-opacity:1"" /_
_rect x=""0"" y=""45"" rx=""2"" ry=""2"" width=""#Actual"" height=""10"" style=""fill:%23333333;stroke-width:0;fill-opacity:1"" /_
_rect x=""#Goal"" y=""30"" rx=""2"" ry=""2"" width=""6"" height=""40"" style=""fill:%23888888;stroke:black;stroke-width:0;fill-opacity:1;stroke-opacity:1"" /_
_/svg_"
VAR vObjects = ALL( Sales[Location], Sales[City],Sales[Country],Sales[State/Province] )
VAR vMaxActual = MAXX( vObjects, [Total Sales] )
VAR vMaxGoal = MAXX( vObjects, [Total Goal] )
VAR vXAxisRangeBase = MAX( vMaxActual, vMaxGoal )
VAR vActual = INT( DIVIDE( [Total Sales], vXAxisRangeBase ) * 90 )
VAR vGoal = INT( DIVIDE( [Total Goal], vXAxisRangeBase ) * 90 )
VAR vReturn = SUBSTITUTE( SUBSTITUTE( vBaseText, "#Actual", vActual ), "#Goal", vGoal )
RETURN IF( [Total Sales], vReturn, BLANK() )
Also, I'm on the September 2018 release of PBI Desktop.
Thanks for your help!!!
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |