Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all please help.
I have a monthly KPI data like belowing.
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.
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.
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 number) again 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:
Insert the Sparkline that you desire:
Enter the corresponding Name that you created in the Name Manager:
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.
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