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,
After reading a lot of blog posts about this and some questions on the community site, I still seem to not be able to figure this one out.
I am trying to depict weekly/daily (based on what people select) crowd data filtered by data for 2 locations.
The crowd trackers measure by timepoint that someone crosses the entrance and then either add or substract users for that specific timepoint based on the movement, per location.
Now I want to create figure that shows these running totals by day for a selected location and a selected year/week (obligated selection). Then the selection of a specific day in that week is optional.
The data basically comes down to this example:
And the resulting figure that I would like to show is basically this, only turned into a line graph:
I have managed to create a running total and make the figure for one date export, but as soon as I start adding more days, I just do not seem to be able to find the right filters in the running total to reset the running total daily. I have linked the date to a date table btw. (line graph with time on x-axis and running total on y-axis filtered for a location).
Totaal running total in Date =
CALCULATE(
SUM('statistics-visits-1598962226601'[Totaal]),
FILTER(
ALLSELECTED('statistics-visits-1598962226601'[Date]),
ISONORAFTER('statistics-visits-1598962226601'[Date], MAX('statistics-visits-1598962226601'[Date]), DESC)
)
)
Any suggestions?
Thank you in advance.
Solved! Go to Solution.
The issue you are seeing is because your Time column has seconds granularity. With multiple days in the visual, that means many thousands of calculation. Also, you should delete the DateTime column, as you have done a good thing to split out the Date and Time columns already. I would round your Times to minutes or even better 5 or 10 minute values for visualization/analysis (and then delete the original Time column too). I made a 10 min round column to confirm with this column, but the permanent fix should be done in the query editor.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
How did this work out @IMRGZ ?
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Thanks for the help! Summing the data per half hour has indeed done the trick.
The issue you are seeing is because your Time column has seconds granularity. With multiple days in the visual, that means many thousands of calculation. Also, you should delete the DateTime column, as you have done a good thing to split out the Date and Time columns already. I would round your Times to minutes or even better 5 or 10 minute values for visualization/analysis (and then delete the original Time column too). I made a 10 min round column to confirm with this column, but the permanent fix should be done in the query editor.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Dear Icey,
I have created two example data links, the first with the dashboard where I inserted the formula for cumulative data and the second with the data:
https://surfdrive.surf.nl/files/index.php/s/xu7ULYYhgm0HPNf
https://surfdrive.surf.nl/files/index.php/s/79nmrdbTXWVqf7N
I have also created a simple example of what I would like to create:
Hope these examples are clear.
Kind regards,
Ivo
Hi @IMRGZ ,
I am curious if you have solved this problem.
If it is solved, can you share your solution with us? Or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let us know.
Best Regards,
Icey
Hi Icey,
Regrettably I have found no solution yet.
The measure mentioned by @amitchandak seems to run into trouble when provided with too much data.
I still hope that someone is able to help with this problem.
Kind regards,
Ivo
Hi @IMRGZ ,
Maybe you can share some sample data, not real data, publicly. Then we can do some test for you.
Best regards
Icey
@IMRGZ , Can you share your formula? Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.
@IMRGZ , If you want a reset daily and cumulative hourly/time
CALCULATE(
SUM('statistics-visits-1598962226601'[Totaal]),
FILTER(
ALLSELECTED('statistics-visits-1598962226601'[Date]),
'statistics-visits-1598962226601'[Date]= MAX('statistics-visits-1598962226601'[Date])
&& 'statistics-visits-1598962226601'[TIME]= MAX('statistics-visits-1598962226601'[TIME])
)
)
@amitchandak Thank you for your swift reply.
I tried this indeed but I seem to get an error with the time dimension:
"A single value for column 'Time' in table 'statistics-visits-1598962226601' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Does this mean that I should narrow the data down first?
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.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |