Forum Discussion
Sum for filtered in distinct value
Hello az38 , thanks for the revert. i had run your code, it give me a constant value regardless of date
On modifying the code slightly i.e. removing the filter expression ALL(Query), this gave me the correct output as expected
Total PAX = CALCULATE(SUMX(SUMMARIZE('Query1','Query1'[Sec], "Total PAX", MAX(Query1[PAX])), [Total PAX]))
Community ChampionHi jjohnson1990
I completely do not understand what is your desired output based on your datasample?
if you need sum by sectorkey you can create a measure like
CALCULATE(SUM(Query1[PAX]), ALLEXCEPT(Query1, Query1[SectorKey]) )Hello az38 , thanks for the revert. i had run your code, it give me a constant value regardless of date
On modifying the code slightly i.e. removing the filter expression ALL(Query), this gave me the correct output as expected
Total PAX = CALCULATE(SUMX(SUMMARIZE('Query1','Query1'[Sec], "Total PAX", MAX(Query1[PAX])), [Total PAX]))- aj1973
Hi jjohnson1990
I noticed that except the column CODE the rest of the values for duplicat SEC are the same! why don<t you just try to remove the Duplicate rows from your table and do the SUM?
hello aj1973,
the above table is only a synopsis of the table data. the duplicate data comes due to a left join condition in sql which cannot be avoided due to data beign pulled in from other tables.
Hello az38 ,
you would see wihtin the table that the row for sectorkey [Sec] is duplicated for sectorkey= 16 and 23, where PAX value is also duplicated.
for sum(pax) , i want to take only of the duplicate values, that is distinct(sectorkey). i believe allexcept would totoally remove that sectorykey values, but i need to take only one (or distinct ) of it.
- az38
if I understand your needs correct, try a measure
Total PAX = CALCULATE(SUMX(SUMMARIZE('Query1','Query1'[Sec], "Total PAX", MAX(Query1[PAX])), [Total PAX]), ALL(Query1))
