Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have found a very useful sparkline measure that works well on a daily basis. I am now wanting to tweak - and attempt to come up with a annual sparkline.
As in the image shown - I have a calendar yr slicer - and then in the right most image, is a measure reflective of the slicer - and the hope is to have a sparkline showing values as represented in the matrix. End result - is when I select a year - the measure value changes, but sparkline continues to show the annual trend..
Below is where I am at on YR Sparkline. I'm guessing the problem is within the Summarize function.
Sparkline Line x Yr =
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year]))
VAR XMaxDate = calculate(MAX('Calendar'[Fin Year]),all('Calendar'[Fin Year]))
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),CALCULATE([Measure Value])),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),CALCULATE([Measure Value])),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
SUMMARIZE('Calendar','Calendar'[Fin Year]),
"X",INT(100 * DIVIDE(max('Calendar'[Fin Year]) - XMinDate, XMaxDate - XMinDate)),
"Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL =
"data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
"<polyline fill='none' stroke='" & LineColor &
"' stroke-width='20' points='" & Lines &
"'/></svg>"
RETURN
SVGImageURL
The original daily sparkline measure is working - and is also found in provided file. As far as I know - the Variables with XMinDate,XMinValue, etc connotations are working properly. The only thing different betwen the two measures - I added a calendar table so I could work with year
File link: https://1drv.ms/u/s!AmBVCme14p7xlV_uC0f_E0payT3N?e=Yp8sPw
Solved! Go to Solution.
This is how the annual sum would potentially look like . Stroke width seems excessive 🙂
Sparkline Line x Yr =
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year]))
VAR XMaxDate = calculate(MAX('Calendar'[Fin Year]),all('Calendar'[Fin Year]))
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
SUMMARIZE('Calendar','Calendar'[Fin Year]),
"X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)),
"Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL =
"data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
"<polyline fill='none' stroke='" & LineColor &
"' stroke-width='20' points='" & Lines &
"'/></svg>"
RETURN
SVGImageURL
Here is another version that is a bit leaner. Keep in mind that the sparkline is not shown by year (or date) so the filter context is basically only the totals.
Sparkline Line x Yr =
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = MIN('Calendar'[Fin Year])
VAR XMaxDate = MAX('Calendar'[Fin Year])
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = MINX(VALUES('Calendar'[Fin Year]),[Measure Value])
VAR YMaxValue = MAXX(VALUES('Calendar'[Fin Year]),[Measure Value])
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
SUMMARIZE('Calendar','Calendar'[Fin Year]),
"X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)),
"Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL =
"data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
"<polyline fill='none' stroke='" & LineColor &
"' stroke-width='2' points='" & Lines &
"'/></svg>"
RETURN
SVGImageURL
There was a small typo in the DAX you copied from @lbendlin - the colon was converted to text and needs to stay as colon when defining the VAR SVGImageURL = data:image See the screenshot posted by @lbendlin rather than the code that was altered when the html of the post was auto edited.
Also, it looks like you are trying to make this work to display all years even when only 1 year is selected, so you need to use ALL (not ALLEXCEPT). @lbendlin I believe has not used a slicer to select a single financial year.
Finally, the measure below will find the MAX year in your Calendar table, but you may wish to use MAXX instead if you want max year that actually has data for it.
See if this measure below helps: replace the bit in RED below with an actual colon :
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
What do you want the annual value to be ? the average of the daily values? the sum? something else? Your code example seems to imply sum.
Small comment on your original measure, lines 8 and 9:
This is how the annual sum would potentially look like . Stroke width seems excessive 🙂
Sparkline Line x Yr =
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year]))
VAR XMaxDate = calculate(MAX('Calendar'[Fin Year]),all('Calendar'[Fin Year]))
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
SUMMARIZE('Calendar','Calendar'[Fin Year]),
"X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)),
"Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL =
"data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
"<polyline fill='none' stroke='" & LineColor &
"' stroke-width='20' points='" & Lines &
"'/></svg>"
RETURN
SVGImageURL
Here is another version that is a bit leaner. Keep in mind that the sparkline is not shown by year (or date) so the filter context is basically only the totals.
Sparkline Line x Yr =
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = MIN('Calendar'[Fin Year])
VAR XMaxDate = MAX('Calendar'[Fin Year])
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = MINX(VALUES('Calendar'[Fin Year]),[Measure Value])
VAR YMaxValue = MAXX(VALUES('Calendar'[Fin Year]),[Measure Value])
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
SUMMARIZE('Calendar','Calendar'[Fin Year]),
"X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)),
"Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL =
"data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
"<polyline fill='none' stroke='" & LineColor &
"' stroke-width='2' points='" & Lines &
"'/></svg>"
RETURN
SVGImageURL
Than you very much - with the help of @AllisonKennedy I was able to make work. Thank you for your awesome support in this community. Thank you
Thank you for your response. Apparently in my orginal post - the image below failed to make it. I would expect to have 5 bars (2010,2011,etc) in the sparkline representing the sum of each year (as shown in the matrix). The sparkline that is working - is the original sparkline by date.
Yes, I am fearful of the line width also - but can only tackle once I see what is going on.
I saved my latest efforts in the same file you have access to.
Again - thank you!!!
I did insert both of your formulas - and in both cases, the result was a blank image. I do have set as an image url
Sparklines are not bars, they are lines. For bars you need different code.
https://www.rgraph.net/svg/bar.html
Here is what I see
Yes, you are right - sparklines are lines :)...
When I copy your two measure in - this is what I see - see the red cirlcled below the matrix w/values. I have also verified both measures are set to an image url
I was reading - this problem could be related to browser - I am using Chrome - and should be current.
thank you again!
There was a small typo in the DAX you copied from @lbendlin - the colon was converted to text and needs to stay as colon when defining the VAR SVGImageURL = data:image See the screenshot posted by @lbendlin rather than the code that was altered when the html of the post was auto edited.
Also, it looks like you are trying to make this work to display all years even when only 1 year is selected, so you need to use ALL (not ALLEXCEPT). @lbendlin I believe has not used a slicer to select a single financial year.
Finally, the measure below will find the MAX year in your Calendar table, but you may wish to use MAXX instead if you want max year that actually has data for it.
See if this measure below helps: replace the bit in RED below with an actual colon :
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thank you Allison - for assisting in the annual sparkline question. This forum is great - because of people like you. Thank you
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |