Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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.
@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.
here are a couple of examples based on your sample data. What is your expected outcome?
Sum:
Average:
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
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.
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.
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.
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...
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/
My week logic is based on this DAX code:
and colums:
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
@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
That sounds like standard Power BI functionality. Where exactly are you stuck?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.