Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors