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
Anonymous
Not applicable

Using KPI Visualisation to visualize trends of change across consecutive indexes

I want to calculate the % change (usually across consecutive weeks) based on selected filter values and visualize them. The idx column represents the week number, the highest number being the most recent week (57 is the highest idx value). 

Under the hood, I want to group by index values, look at the sums of 'Job Count' for these groups, calculate the percentage change across the sums corresponding to consecutive indexes, and visualize this trend in the background of the KPI visualization. This weekly (usually) % change would be calculated on-the-fly based on custom values of filters etc. Can you please help me out with this? I have attached a snapshot of the data. Please let me know what other info you'd like me to provide. Screen Shot 2022-08-09 at 9.59.30 am.png

2 REPLIES 2
Luca_Yu
Regular Visitor

Hi @Anonymous ,

I create sample based on my understanding according to you describe, please check if it meets your needs.

My table:

Luca_Yu_1-1660201070713.png

Create a measure:

Percent Change =
VAR cur_index =
    SELECTEDVALUE ( 'Table 2'[Index] )
VAR pre_index = cur_index - 1
VAR cur_jc =
    CALCULATE ( SUM ( 'Table 2'[Job Count] ), 'Table 2'[Index] = cur_index )
VAR pre_jc =
    CALCULATE ( SUM ( 'Table 2'[Job Count] ), 'Table 2'[Index] = pre_index )
VAR diff_jc = cur_jc - pre_jc
RETURN
    DIVIDE ( diff_jc, pre_jc )

Create a KPI and Slicer visual, then add the fields and measure:

Luca_Yu_0-1660201007470.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hey Luca, thanks for your reply. I have already implemented something like this. I am able to create measures for the two most recent values and calculate the % change between them as you have in your comment. However, I am trying to group by the 'idx' column (which takes values 1-57 representing the week number), calculate the % change of sums of Job Counts between two consecutive indexes/weeks, and map the 56 values for these % changes as a plot in the background of the KPI visualization.

 

So, in the KPI viz, I'd like the 'Trend' plot in the background to be a plot of the % change (y-axis) by week number (idx, x-axis). So, in your example, it's % change between 2 weeks just. I'd like it to be a plot for 57 weeks (56 data points) for % change between consecutive indexes. Please let me know how you would do that, or if you have any questions. Thanks.

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