sum of max

3 Topics

Sum of maximal value for specific name
Moving from (pivot)tables in Excel to Power BI provides some struggles. I will try to show my struggle as detailed as needed. The case: as a KPI (Key Performance Indicator) in sports I need to know from longitudinal data IF a player passed the benchmark or not (providing a % by dividing passed/all cases). He only needs to pass the benchmark once during the year (multiple matches obviously; benchmark can be different depending on playing position). Name Match Total distance covered (m) Benchmark (m) Passed (o=no;1=yes) Player A 1 6000 5000 1 Player B 1 4000 5000 0 Player C 1 7000 6000 1 Player D 0 ** 1500 - - Player A 2 5500 5000 1 Player B 2 4000 5000 0 Player C 2 5000 6000 0 ** not enough playtime, so data is there, but not included for the KPI. KPI = passed/total In Excel I would insert a Pivot table on the table above and then use "Max of passed" providing for each player if he ever passed the benchmark and total amount of filled cells (so unique players in this pivot table). The pivot table in this example would look like: Name Max of passed Player A 1 Player B 0 Player C 1 KPI: 2 out of 3=66.7% Player D is not included in pivot table, since there is no valid data (0 or 1) Sum of Max of passed is needed, since 'just' sum of passed will provide 2 instead of 1 for player 1. In Power BI I provided a new measure "amount_players" which is: DISTINCTCOUNT(query1[player]). This will show the amount of players that pass multiple filters (minimum of playing time, team and season). Now I need a measure "amount_passed" but using MAX(X), SUM or a combination does not deliver the right answer. The main struggle appears when a player is able to pass the benchmark for 3 matches; any use of sum wants to enter the player with a '3', but I only want to count him once. If I need to provide more context or sample data, please let me know!
Solved
Maestro
5 years ago Place DAX Commands and Tips
953Views
0likes
3Comments
SUM of MAX values in SUMMARIZE function
Hi! I think i'm missing some DAX function, i have this code for a calculated table: SUMMARIZE('produccion chat y ret','produccion chat y ret'[C-OP],'produccion chat y ret'[N-COLADA-O],'produccion chat y ret'[W-Q-TONELA-O],"fecha mov",MAX('produccion chat y ret'[Fecha MOVTO])) and is returning me this table (filtered to be more clear): and i wanted to SUM the tons for the MAX date of my table so i tried this code: SUMMARIZE('produccion chat y ret','produccion chat y ret'[C-OP],'produccion chat y ret'[N-COLADA-O],"fecha mov",MAX('produccion chat y ret'[Fecha MOVTO]),"tns",SUM('produccion chat y ret'[W-Q-TONELA-O])) but it's returning me the total sum: it should only sum the first two rows with date of 26/08, how can i reach this result? Thank you for your help!
Anonymous
6 years ago Place DAX Commands and Tips
1.1KViews
0likes
2Comments
SUM of Max values
Hi all, I want to write a dax measure for following table. I write a sql query that gives the correct result, but I could not convert to dax format. I tried following dax, it does not give correct result. By the way, I am working on direct qurey and want to filter my data according to date via using slicer(between type). Please , pay attention to these conditions when you write a solution. SQL: select sum(total) from(select p_id,m_id,max(value) as total from table group by p_id,m_id) DAX:(not correctly result) SUMX( SUMMARIZE(table,table[m_id],table[p_id], "Total", MAX(table[value])),[Total]) As you can see, every m_id has p_id and table includes more than one value for different date. Table: m_id p_id value date 1 23 10 2020-01-10 1 23 20 2020-01-11 2 23 50 2020-01-10 4 123 10 2020-01-15 2 23 10 2020-01-05
Anonymous
6 years ago Place DAX Commands and Tips
1.7KViews
0likes
7Comments