Power BI report compare multiple rows to find growth percent

[KJ]

Hi,

I am new to power BI and trying to figure out things, But dont have time in hand want to design a simple report to show percentage value increased  over time for a resource

I am pulling data from sharepoint online list, I got the data in Power BI.

This is the sample data in which i have shown 4 items with three enteries on different date, I have 600 items with multiple date enteries. I want to show a bar graph or line graph with date slider on top to show which top 10 items have grown maximum in the selected date period.

I have tried to search tutorials and look for similar example but could not find much, Please share explanatory steps as i am not much familiar with functions in power bi.

Data Schema.

Site	JobRunDate	Value
A	3-Apr-17	203050
B	3-Apr-17	1254
C	3-Apr-17	321
D	3-Apr-17	444
A	10-Apr-17	203350
B	10-Apr-17	1294
C	10-Apr-17	500
D	10-Apr-17	555
A	17-Apr-17	205550
B	17-Apr-17	1344
C	17-Apr-17	800
D	17-Apr-17	666

Thanks,

KJ 

[bblais · Accepted solution]

I  created a JobRuns table with your sample data in it:

First I created measures for the MIN and MAX values.  Because they are measures, they will calculate within a given context, so they should give you MIN/MAX for whatever date range you filter by (and site).  Be careful not to use these without a site filter or they will potentially give you incorrect data:

Min Site Value = MIN(JobRuns[Value])

Max Site Value = MAX(JobRuns[Value])

Now just add a measure to calculate the percent change.  Again, since this is contextual, it will give you the percent change over whatever time period you have sliced:

Pct Increase = DIVIDE([Max Site Value]-[Min Site Value],[Min Site Value])

 With some formatting, here's a table that shows your percent increases for each site.  To show it works with the date filters, I chose only 4/10 and 4/17:

You could always safeguard it from trying to calculating a percent increase when you don't have specific sites by doing something like this to just return blank if more than one site is chosen. This would work on the MIN/MAX measures too:

Pct Increase = IF(HASONEVALUE(JobRuns[Site]),DIVIDE([Max Site Value]-[Min Site Value],[Min Site Value]))

[KJ]

Thanks for the reply.

I have been able to see some thing but still not there yet.

What i have done as you said i have added three measures : Maxsiteval , minsiteval , pctIncrease 

Values column has Siteurl , Maxsiteval , minsiteval , pctIncrease

filter has : Maxsiteval , minsiteval , pctIncrease , siteurl with top 20 

I can see the result with max and min values but percentage column is coming zero , trying to figure what did i miss.I did not get what you mean by "Be careful not to use these without a site filter or they will potentially give you incorrect data:"

Thanks

KJ

[bblais]

Can you post an image of your table visual that has all the fields you want, including the % increase field that is showing as 0%? 

[KJ]

Here is the image 

Filter only siteurl is not showing in the screen shot, It has a selection of top 20. 

Please let me know if you need any more information.

Thanks a lot for your help

[KJ]

I moved a step further, So i can see the comparision between max and min value.

Now i want is to sort by highest percent increase ( Desc-Asc) but do not see a option to sort the result

Second thing can we change the type showing on the value axis, By default it takes it as Million value , I would want to show it in Megabytes.

Third is the filter on the top lets me select one date not multiple , i have changed the type to date time now it shows as slider

Thanks,

KJ

[bblais]

In your stacked area chart, click the ellipses (...) in the upper right-hand corner, and you should be able to choose your pct increase column for sorting.  To sort descending you may need to click and choose the column twice, once to sort ascending and then again to switch to descending.

[bblais]

To change the units on the Y-axis, you can go into the visual properties and change the Y-Axis Display Units value:

[KJ]

Thanks bblais, all your help is really appretiated mate.