Customer calculation by Filter/Measure

[ajbogle]

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)

[Anonymous · Accepted solution]

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.