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
Dellis81
Continued Contributor
Continued Contributor

Annual sparkline

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

2 ACCEPTED SOLUTIONS

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&colon;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&colon;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

 

View solution in original post

@Dellis81 

 

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&colon;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 :

 

Sparkline Line x Yr1 =
//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]))
//calculate(YEAR(MAX('Calendar'[Date])),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]),ALL('Calendar'[Fin Year]))
VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALL('Calendar'[Fin Year]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS( SUMMARIZE(ALL('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]," ",[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = "data&colon;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

Please @mention me in your reply if you want a response.

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

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

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:

 

lbendlin_0-1597527374758.png

 

 

 

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&colon;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&colon;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

 

Dellis81
Continued Contributor
Continued Contributor

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

Dellis81
Continued Contributor
Continued Contributor

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!!!Sparkline Daily to Annual.PNG

 

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

 

lbendlin_0-1597536800797.png

 

Dellis81
Continued Contributor
Continued Contributor

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!

 

SparklineAnnual2.PNG

@Dellis81 

 

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&colon;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 :

 

Sparkline Line x Yr1 =
//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]))
//calculate(YEAR(MAX('Calendar'[Date])),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]),ALL('Calendar'[Fin Year]))
VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALL('Calendar'[Fin Year]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS( SUMMARIZE(ALL('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]," ",[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = "data&colon;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

Please @mention me in your reply if you want a response.

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

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.