Forum Discussion
Customer calculation by Filter/Measure
Hello all,
I'll do my best to explain my desired result. I'm working on a personal project, and I want to create either Measures or Filters that can effect specific columns in a table.
Current Table: Total - Player Stats
This is my 'Total' (period) table under the 'Core' (stats). I want to create a custom measure or filter that would effect certain columns by the end user pressing 5-Min, Game or Series. Currently, I've created 4 tables (Total, 5-Min, Game, Series) and I can get my desired result by using bookmarks, and having measures for each period.
5-Min Player Stats
Per-Game Player Stats
As you can imagine, having to create 4 tables with measures, and bookmarks for each 'Stat' category would be not only time consuming, but I think inefficient. Is there a way to have one table for each stat group as a SUM(Total) and then have a custom measure to change the calculation based on period?
5-Min = Total Value (Score, Goals, Assists, Saves, etc.)/300(seconds)
Per-Game = Total Value (Score, Goals, Assists, Saves, etc.)/total games played
Series = Total Value (Score, Goals, Assists, Saves, etc.)/total matches played (seperate fact table)
Hi ajbogle ,
It seems that the visuals in different bookmarks contain the same fields ,but with different calculations.
I'd suggest you create a seperate table with values ——Total, 5-Min, Game, Series for slicer later:
For Slicer = {"Total", "5-Min", "Game", "Series"}Then create measures for each field, below is based on my data sample:
total games played = CALCULATE(SUM('Table'[Played]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player])))Score = var _sum=CALCULATE( SUM('Table'[Score]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player]))) return SWITCH(MAX('For Slicer'[Value]),"Total",_sum ,"5-Min", _sum/300,"Game",_sum/[total games played],"Series",_sum /[total matches played]) Goals = var _sum=CALCULATE( SUM('Table'[Goals]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player]))) return SWITCH(MAX('For Slicer'[Value]),"Total",_sum ,"5-Min", _sum/300,"Game",_sum/[total games played],"Series",_sum /[total matches played]) Assists = var _sum=CALCULATE( SUM('Table'[Assists]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player]))) return SWITCH(MAX('For Slicer'[Value]),"Total",_sum ,"5-Min", _sum/300,"Game",_sum/[total games played],"Series",_sum /[total matches played])Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi ajbogle ,
It seems that the visuals in different bookmarks contain the same fields ,but with different calculations.
I'd suggest you create a seperate table with values ——Total, 5-Min, Game, Series for slicer later:
For Slicer = {"Total", "5-Min", "Game", "Series"}Then create measures for each field, below is based on my data sample:
total games played = CALCULATE(SUM('Table'[Played]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player])))Score = var _sum=CALCULATE( SUM('Table'[Score]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player]))) return SWITCH(MAX('For Slicer'[Value]),"Total",_sum ,"5-Min", _sum/300,"Game",_sum/[total games played],"Series",_sum /[total matches played]) Goals = var _sum=CALCULATE( SUM('Table'[Goals]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player]))) return SWITCH(MAX('For Slicer'[Value]),"Total",_sum ,"5-Min", _sum/300,"Game",_sum/[total games played],"Series",_sum /[total matches played]) Assists = var _sum=CALCULATE( SUM('Table'[Assists]),FILTER(ALLSELECTED('Table'),[Player]=MAX('Table'[Player]))) return SWITCH(MAX('For Slicer'[Value]),"Total",_sum ,"5-Min", _sum/300,"Game",_sum/[total games played],"Series",_sum /[total matches played])Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.