Forum Discussion
Dynamic Cumulative Subtotal for Correlation
I know...I know...you may think this has already been solved in many ways, but I am not seeing the various examples solve this example I will show you below.
I have tried:
Sumx
Filter with Earlier as Column and as standalone measure
Filter with Date <= Max(Date) or within Date Range
I literally feel like I have tried all forum topics here and across PowerPivotPros, DaxPatterns, etc.
Here's what I am trying to achieve...
I'd like to look at a player's cumulative minutes played by each date and same with the team totals.
In the example below I want zTeam_CTrlMins to show the cumulative total minutes of zTeam_TtlMins. Ideally it will be dynamic to the slicer date range selected. So for zTeam_CTtlMins with these 4 dates it will go:
192 on 1/8/18
144 on 1/7/2018
96 on 1/5/18
48 on 1/2/18
Same with the individual zMinsPlayed
119
89
61
34
I can probably then carry the solution forward on the other stat categories, but none of the solutions I am finding online are creating the answer. Here is the measure I have in zTeam_CTtlMins:
zTeam_CTtlMins = calculate(sumx(values(TeamSummary_Table[Team]),[zTeam_TtlMins]),filter(all(TeamSummary_Table[Date]),TeamSummary_Table[Date]<=max(TeamSummary_Table[Date])))
I would also like to create a way to show last 7 games, 5 games and 3 games. I have tried the TOPN, but it fails horribly. So if I selected Dec 2017 and Jan 2018 I would want it to smartly pick up that players last (x) based on the selection.
3 Replies
- Ashish_MathurSuper User
Hi,
Your question is not clear. Please share a dataset and show the expected result there.
- rymercoFrequent Visitor
Sorry about the confusion Ashish and community, but your help is appreciated.
Link to the PBi file: https://www.dropbox.com/s/qrv8laj1qxhqcy2/PlayerMins_PowerBI.v1.pbix?dl=0
Here is a snapshot of the goal:
Here is a snapshot of the PowerBi file:
- Greg_DecklerCommunity Champion
Well, without actual sample data, pretty hard to give you an exact answer.
But, what you are describing seems like a Running Total quick measure or some slight variant would work just fine. I would probably use a SUMMARIZE to summarize the running totals into some new tables and then use your standard pearson correlation, the technique for which was originally published here ages ago (2 years ago):
and before that published in 2015 here:
And just recently recycled as a Quick Measure in the January release of Desktop and accredited to someone else. But I'm not bitter or anything. :)