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
awawaw
Helper I
Helper I

Data type

Dear all please help.

I have a monthly KPI data like belowing. 

awawaw_1-1657716212654.png

1. I want to present this data in the same way and add a sparkline in the end of each row. 

I know there is a function "add a sparkline," but in this data format, the function can't provide me the right sparkline. 

I also tried to transpose the data and build sparkline for each row. The only problem is once someone sort the table, the chart will mismach with each row. 

2. Is there any way that I can adjust the data type by row?

Metric1 is whole number, and Metric2 is percentage. As far as I know, I can only change data type by column. Is there any way to achieve my goal?

 

Please help

Really appreciate. 

 

1 REPLY 1
Knighthawk
Helper I
Helper I

Hi awawaw,

 

I am unsure about the issue referenced regarding the right sparkline, but I hope that the following steps will provide you with the a solution to your issue of referencing the set of cells based upon the row rather that the specific cell references that change as the data table updates.  I have provided screenshots below for each step.

 

In my example below I am using the "Name Range" feature in Excel, which you will find under the Formulas tab.

 

Excel - Name Manager 20220714.png

 

Excel - Name Manager - New 20220714.png

 

 

You can of course name this anything that you would like; however keep in mind that this will need to be done for every sparkline that you create, so you may want to create a naming system that will make it easy to reference both in the Name Manager, and when entering this name reference in each Sparkline Data Range field.  The "Refers to" will include the following formula (adjusted to the range that you desire):

 

=INDIRECT("Sheet1!"&ADDRESS(4,2)&":"&ADDRESS(4,8))

 

-  Where "Sheet1!" references the worksheet name where the data range is located, followed by the exclamation mark to separate the worksheet name from the cell reference.  (Note:  If you have a more complex worksheet name you may need to reference the worksheet as 'Example Name'! which will include the apostrophe before and after the name).  You will also need the & between the worksheet name and the next part (ADDRESS...)

 

ADDRESS(row number, column number) for the first cell within the desired range, followed by &":"& and then ADDRESS(row number, column numberagain for the the last cell in the desired range.  Keep in mind that the second variable in the ADDRESS function is the column number, so for example the number for column A is 1 and for column B is 2, and so on.  So in this case seen in the screenshot below, the range is B4:H4 on Sheet1:

 

Excel - Name Manager - New Spark Name 20220714.png

 

 

Insert the Sparkline that you desire:

 

Excel - Insert Sparkline 20220714.png

 

 

Enter the corresponding Name that you created in the Name Manager:

 

Excel - Sparkline Data Range 20220714.png

 

 

I unintentionally set the reference of the name range to include the CaseId and RunDate columns, so the Sparkline shows some interesting blips initially due to these incompatible values; however, you will notice in the second screenshot below, that when the a new row of data is entered into the first row, the Sparkline updates accordingly with that data in the first row, rather than following the data down to the second as you were running across.

 

Excel - Sparkline Data Result 1 20220714.png

 

 

Excel - Sparkline Data Result 2 20220714.png

 

I hope that this information is helpful and provides the solution you were looking for in relation to your inquiry.  Should you have any questions, please do not hesitate to follow up with any questions that you might have.

 

Best Regards

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.

Top Solution Authors
Top Kudoed Authors