Hello everyone, this is my first message, and it is a pleasure to be able to participate in the community.
Maybe someone can guide me on some problems I am encountering, trying to perform simple calculations
The data detailed in image 1 is a table that has information related to the monitoring of players with GPS in different training sessions
But as the majority who work with Power BI, work with more business related topics, I will try to describe the problem first in terms of sales and sellers. And then with the data that I use.
Sales
Suppose we have 4 sellers and each one makes 3 sales in a day and each sale corresponds to three different categories and we want to know.
Distances traveled
Suppose we have 4 Athletes and each one performs 3 Drills in one day and each Drill corresponds to three different categories and we want to know.
1. What was the total sale for each seller in dollars.
1. What was the total distances for each Athleta in meters.
2. Based on the total sale of each seller, calculate the average sold for the day.
2. Based on the total distance of each Athleta, calculate the average distance traveled for the day.
3. Calculate the average sales per product category sold on the day.
3. Calculate the average distance for each Drill performed on the day.
4. Calculate the percentage difference between the total sales of each seller in relation to the average sales for the day.
4. Calculate the percentage difference between the total distance traveled by each Athleta in relation to the average distance of the session.
5. Calculate the percentage difference that each seller had in relation to the average sales by category.
5. Calculate the percentage difference of the distance traveled by each player in each Drill, in relation to the average of the drill of all the Athletes.
6 Calculate the total sales for the day, the total sales per seller, the total sales by product category.
6. Calculate the total distance for the day, the total distance for Athleta, the total distance for Drill
7. Calculate the percentage of sales of each seller in relation to the total.
7. Calculate the percentage of the distances covered by each player in relation to the total
8. Calculate the percentage that the sale of each item occupies in the daily total of the seller.
8. Calculate the percentage that the distances traveled in each Drill occupies the total of the distances traveled by the Athleta
9. Calculate the standard deviation of sales for the day. Using the total sales made by each seller.
9. Calculate the standard deviation of the training session. Using the total distance traveled by the player.
10. In the case of having several days of sale, Calculate the average sales for each day, and calculate the percentage of sales that each day occupies in relation to the total.
10. In the case of having several days of training, after calculating the average distance of each day, calculate what is the percentage that each session occupies in the total of the x sessions.
If we pass this to terms related to GPS data, we have 20 Player (seller), who perform X Drills (Category) in each training session. In each Drill (Category), each Player (seller) travels a certain Distance (meters).
Session = Is the code of each training session
Drill = Exercises that each player performed in each training session.
Athleta Code = The code of each athlete.
Position = the position in which each athlete plays.
Date = It is the date for each, session, exercise, athlete, etc.
Time = Is the time that each Drill started.
Duration = It is the time that each exercise lasts.
Day Code = It is a code that is used to guide how many days the next game is.
DIstance = In this case it is the variable that I want to use as an example.
All the fields, except "Distance", are intended to be dimensions in order to analyze the data.
I am trying to design formulas that work well on granularity. Thinking of being able to do calculations with 1 or more sessions simultaneously, and thinking of being able to enter within each level, and that the formula continues to work well, not only for the specific value of the distance, but also for the percentage.
Example of Granularity> Here you can see it as session> Player Index> Participation> Drill
D_F2 SumPrPr Slicer =
averagex (
KeySessionTable,
AVERAGEX (AthleteTable,
CALCULATE (SUM (GPSdataTable [Distance]))))
Calculate the sum of the session for each player, and generate the average of the session for all. The total shows the average of all the selected sessions. And it responds to all filters
When I want to know the total of all the sessions, I selected the following formula: formula works well at all levels
D_F3 SumPrSum Slicer =
sumx (KeySessionTable,
averagex (AthleteTable,
CALCULATE (SUM ('GPSdataTable' [Distance]))))
Calculate the sum of the session for each player, and generate the average of the session. But in the total he adds me the averages of each session. And it responds to all filters.
This third formula aims to have the average of all the selected Athletes at all levels of granularity and thus be able to use this formula to calculate percentages in the future.
D_F4 PrPrPr Ath Slicer =
IF (ISBLANK ([D_F0 SumTot Slicer]), BLANK (),
Averagex (KeySessionTable,
AVERAGEX (
ALLSELECTED ('AthleteTable'),
CALCULATE (SUM ('GPSdataTable' [Distance])))))
Displays the average of ALL athletes who are selected at all levels of granularity. And it responds well to other slicers.
This fifth formula works the same as the fourth, but the average appears based on all the athletes who participated in the session, a weight that is not selected in the filter. And like the previous formula, it could be used to calculate percentages.
D_F5 PrPrPr All_Ath =
IF (ISBLANK ([D_F0 SumTot Slicer]), BLANK (),
Averagex (KeySessionTable,
AVERAGEX (
ALLNOBLANKROW ('AthleteTable'),
CALCULATE (SUM ('GPSdataTable' [Distance])))))
Shows the average of ALL athletes at all levels. It does not matter that they are not selected. It responds well to all slicers.
So far, I am getting the data I want, but I must be honest, when I want to start working percentages, the results are not entirely good the moment I enter and exit the granularity.
I would like to know, if any that really separates from all this, if the formulas are well written. sin are efficient, since "Distance" is one of many other variables.
From already thank you very much.
Diego
Please don't forget your Kudos if you don't mind. Thanks.
OK, I'll try to modify the statement and the attached information.
Thank you.
Diego
User | Count |
---|---|
40 | |
31 | |
28 | |
25 | |
17 |
User | Count |
---|---|
55 | |
42 | |
39 | |
35 | |
29 |