cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alindv
Frequent Visitor

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 @alindv ,

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.

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors