Calculating daily difference on network throughput

[Anonymous]

Hi all,

We're using Power BI for something different than what it normally is used for - network analysis. The principle of course stay the same.

My issue is that I have multiple countries, which consists of multiple regions in those countries, which contain a different number of servers, where each server have a certain amount of throughput. I have the following values in my table:

date | server name | network throughput | region | country

I'm trying to find a method how I can differentiate between the network utilization on a daily, weekly and monthly basis, and it can be either expressed as a value or a percentage. Currently I have created a calculation whereby all the servers in a region are summed together to give a total throughput per region per day, so that is also an option to decrease the amount of filtering to be added. Ideally it would be great to have these done per server.

Here is an example of the type of data:

Date	server name	throughput	Region	Country
01-May-20	server10.ams	6.65502165	Amsterdam	Netherlands
01-May-20	server11.ehv	0.466185961	Eindhoven	Netherlands
01-May-20	server2.zrh	8.566098994	Zurich	Switzerland
01-May-20	server4.urh	4.746088086	Utrecht	Netherlands
01-May-20	server11.ams	2.380500813	Amsterdam	Netherlands
01-May-20	server3.ldn	11.64876059	London	United-Kingdom
01-May-20	server1.bhm	14.45529932	Birmingham	United-Kingdom
01-May-20	server1.brt	0.773632127	Bucharest	Romania
01-May-20	server2.dbn	0.731045812	Dublin	Ireland

Thanks in advance for any help on this.

Regards,

NjR

[Anonymous]

Hi @amitchandak @lbendlin , 

Thank you for your feedback. I've tested all the date formulas (which is super cool btw), but I'm getting stuck everytime on the same issue - whenever I'm trying to manipulate the throughput based on date, I'm getting an error back stating that: "a singe value for column 'throughput' in table 'network' cannot be determined." And then it continues about suggestions as to aggregating the data to get to a single result.

But, I already have a single result - per server which belongs to a specific region in a specific country I have 1 value per day.

What am I missing then? 

[lbendlin]

you're missing the concept of a filter context.  Just because you see a single value (date) on a visual  doesn't mean there is actually physically a single row of the extended table behind it.

So go with the aggregations (sum, average,whatever it appropriate) and use these aggregations as your formula input.

[Anonymous]

@lbendlin , can you perhaps provide me an example? 

Whenever I try to use filters, I end up having to apply filtering to the point where I can only filter a single server (from the list I provided), instead of Power BI automatically picking the unique server and its throughput, and comparing it with another day.

[lbendlin]

here are a couple of examples based on your sample data.  What is your expected outcome?

Sum:

Average:

[Anonymous]

Thanks for your help so far @lbendlin !

Ok, so in the data set I provided, this data is collected every day, in total almost a year where every server's throughput is collected every day. I'm trying to perform a calculation whereby I'm doing a "today is x% more / less than yesterday" and a "this week is x% more / less than last week", and this quarter is x% more / less than last quarter", and so on. At the end of a week / month / quarter I can then generate a report over the different time frames and see if there was a negative growth or a positive growth in network throughput.

And this is where I'm getting stuck - doing the calculation to determine the percentage growth over the different periods

[lbendlin]

This is also standard Power BI functionality.  Have a look at the Quick Measures 

or provide some more representative test data across the desired time spans. 

Caution:  comparing throughput across locations and computing averages feels a bit dangerous as it ignores the available pipe sizes. Apples and oranges, etc.

[Anonymous]

I'm not trying to compare throughput across locations, but rather growth / decline of throughput per region and individual server.

I've tried to use quick measures, but it doesn't provide the results I'm looking for.

In the link (https://docs.google.com/spreadsheets/d/1ak1Z0R40uDlLFEL9JvpWaWzfoRx1qKlCrwAD6KX9ahY/edit?usp=sharing) a bigger data set across a specific month. If you can help point me how I should do the day over day or week over week, then I'll be able to figure out the rest. 

[Anonymous]

@lbendlin , any feedback on this? 

Thank you in advance

[lbendlin]

In your world, what is a week? What is a quarter? Do you have a Calendar table that identifies the boundaries?

Do you want to show the thoughput change just week over week, or week over week to date? 

your second sample file is not accessible.

[Anonymous]

I do have a calendar table where my weeks and quarters are defined, and it is based on a normal year - 1st January to 31st December with the normal 4 quarters. 

The week over week should be based on the week numbers in this calendar (again, based on a normal calendar year) - not week over week to date.

I noticed the sharing somehow was changed - here is the new link: https://docs.google.com/spreadsheets/d/1ak1Z0R40uDlLFEL9JvpWaWzfoRx1qKlCrwAD6KX9ahY/edit?usp=sharing...

[lbendlin]

please post your week logic, and describe what should happen between December and January.

Also refer here https://www.daxpatterns.com/standard-time-related-calculations/

[Anonymous]

My week logic is based on this DAX code:

Date = addcolumns(Calendar( Date(2020, 1, 1), Date(2021,12,31)), "weeknum", weeknum ( [Date] ))

and colums:

Month = Format('Date'[Date], "MMM yyyy")

Quarter = Year('Date'[Date]) & "-Q" & Format('Date'[Date], "q")

Year = Format('Date'[Date], "yyyy")

MonthSort = Format('Date'[Date], "yyyy-mm")

As for December to January, it should loop, so week over week I expect it to do week 1 over week 52, and then week 2 over week 1, etc. Am I understanding this principle correctly?

[Anonymous]

Hi @lbendlin , I'm curious to know if you have managed to look at the data and were able to generate a view for the week over week data?

I'm still not having any luck generating the view, so I would really appreciate your help to guide me into the right direction for this

[amitchandak]

@Anonymous , with date table you can do any kind of comparison

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[lbendlin]

That sounds like standard Power BI functionality. Where exactly are you stuck?