Forum Discussion
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 |
7 Replies
Hello Harsh,
Thanks for reply. I try to explain with following example.
example 1: I selected p_id =23 from filter. Correct result must be 70(max value for each m_id)example 2: If I dont select p_id ,correct result total of value is 80 ( 20+50+10)
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
example 3: This is my case. I want to calculated total value for selected date. For example I selected date between 2020-01-04 and 2020-01-11. Result should be 65 . ( by the way, I changed to value and add 1 row on the table to understand the example better. )m_id p_id value date 1 23 12 2020-01-10 1 23 20 2020-01-11 2 23 50 2020-01-15 4 123 10 2020-01-09 2 23 10 2020-01-05 4 123 30 2020-01-10 3 30 5 2020-01-09
Hi ,
thanks for reply. But it is not working very well. When I select p_id with no date filter, it shows correct answer. But it returns wrong answer when p_id and date filter are chosen. If I share sql query, it can help you about what I want.
**Date and p_id selection will be done via filters in power bi.select sum(total)
from(select p_id,m_id,max(value) as total
from table
where p_id=23 and date between 2020-01-04 and 2020-01-11
group by p_id,m_id)