Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have juste started learning Power BI and I would like to analyze my running race data. I have played around with may data, but I'm not getting my head around this.
Background:
End goal:
Analyzing and comparing all my races by correcting my total time to official time.
Problem 1 - How do I make it as generic and easy to and new data as possible?
Does this sound like a good approach? How do I add a collumn whith the race name?
Problem 2 - correcting my total race time
My race data from my running watch does not match the offical race stats 100%.
I need to:
I don't know how to find how far extra I have run in the query?
I would also like manually add extra time to selected splits. How, when, where would I do that?
Here is an exampel og what I would like to do in Excel (gray is original data, green is shaped data):
Solved! Go to Solution.
Hi @Cavring,
You'd better create a new table unioning the required columns form tables using the formula below.
Table = UNION(SELECTCOLUMNS('1610 Amsterdam maraton',"Løps ID",'1610 Amsterdam maraton'[Løps ID],"Split",'1610 Amsterdam maraton'[Split],"Tid",'1610 Amsterdam maraton'[Tid]),SELECTCOLUMNS('ManuellKorr',"Løps ID",'ManuellKorr'[Løps ID],"Split",'ManuellKorr'[Split],"Tid",'ManuellKorr'[Tid]))
Then create a measure and table shown in screenshot2.
Sum Tid1 = sum('Table'[Tid])
Please download the file form here.
Best Regards,
Angelia
Hi @Cavring,
In order to help you to resolve your problem. I still need to comfirm the following information.
1. In the first screenshot, what do you mean the Time,dist.Adj and Time,manual.Adj columns? where do you import? Based on my understanding, you add Time, Time,dist.Adj and Time,manual.Adj columns together and get the Time new column, right? If it is, you'd better create calculated column.
2. The problem1 and problem2 is general, could you please descriobe it clearly?
3. How you correct your total time to official time? Which column will be used? And what result do you want?
Thanks,
Angelia
1. In screenshot 1 I have the gray data available (Split, Distance & Time). The green colloums are an example in Excel. I would like to this in Power BI, but I am not sure how. Can it be done in the query or if it is best to do it after the import.
If I can add a 'Race name' in the query, I can do all the calculations after that. How can add this name (preferably based on the table name)?
Screen shot from Excel. I need help with creating 'Race name'. Rows 36-42 shows what I would like to do in Power BI. The formulas in collumn 'D' explains what I would like to do.
2. I can work on this a bit more myself before I aske for help. I think I know how I should approach it
3. I think I will add a table nre with 'Official time' and 'Time manual adj.' and use relationships to connect the data through 'Race name'.
Thanks,
Espen
Hi @Cavring,
Please share the resource data for further analysis? So I can reproduce the scenario. Thank for understanding.
For your question1, please add Race name by adding custom column as follows. You can select the add custom in Query Editor. In order to achieve the D column's result, you can create calculated columns.
Best Regards,
Angelia
As for Race name I have soveld it by adding a custoum columns as you say. I enter i Run ID and make a relationship to another table with Run ID and Race name.
I have now come to a problem I can't solve. This is my formula:
Tid final = CALCULATE(sum('1610 Amsterdam maraton'[Tid]);ALLEXCEPT('1610 Amsterdam maraton';'1610 Amsterdam maraton'[Split];'1610 Amsterdam maraton'[Løps ID]))
+CALCULATE(sum(ManuellKorr[Tid]);ALLEXCEPT(ManuellKorr;ManuellKorr[Løps ID];ManuellKorr[Split]))
What I want to do is to add the times [Tid] from two different tables together. Total should be 3:09:00.
Here's the file:
Hi @Cavring,
You'd better create a new table unioning the required columns form tables using the formula below.
Table = UNION(SELECTCOLUMNS('1610 Amsterdam maraton',"Løps ID",'1610 Amsterdam maraton'[Løps ID],"Split",'1610 Amsterdam maraton'[Split],"Tid",'1610 Amsterdam maraton'[Tid]),SELECTCOLUMNS('ManuellKorr',"Løps ID",'ManuellKorr'[Løps ID],"Split",'ManuellKorr'[Split],"Tid",'ManuellKorr'[Tid]))
Then create a measure and table shown in screenshot2.
Sum Tid1 = sum('Table'[Tid])
Please download the file form here.
Best Regards,
Angelia
Hi @v-huizhn-
It worked like a charm. Thank you very much. I will be getting much use from that.
Thanks again,
Espen
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |