Sum for filtered in distinct value

[jjohnson1990]

HELLO ALL,

I currently have a table with below for columns.

Sec	SECTORDATE	DEP DLY MIN	PAX	DEP DELAY CODE
5	1/1/20 0:00	20	167	15 - PH
8	1/1/20 0:00	85	167	91 - RL
10	1/1/20 0:00	15	162	93 - RA
16	1/1/20 0:00	105	131	93 - RA
16	1/1/20 0:00	105	131	31 - GD
21	1/1/20 0:00	15	159	85 - AS
23	1/1/20 0:00	19	154	93 - RA
23	1/1/20 0:00	19	154	34 - GS
24	1/1/20 0:00	35	131	93 - RA

I need to first calculate the sum of pax for the distinct Sectorkey column based on a condition and display the figure in card visualization.

Used the below dax query but could not get the exact answer.

Total PAX =
SUMX (DISTINCT('Query1'[Sectorkey],(Query1[PAX]))

if someone can help me , if you need more informations plese tell me.

[az38]

Hi @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]) )

[jjohnson1990 · Accepted solution]

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?

[jjohnson1990]

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.

[aj1973]

@jjohnson1990 

doesn't matter how it is linked in the source SQL. If you are willing to remove the duplicate then use Power query in query editor to clean and transform your data. Remember, for performance purposes you need to clean, get rid of none necessary data and transform data in Query Editor to get the most in your visuals and the use of DAX in the Power BI desktop.

Regards 

[jjohnson1990]

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]

@jjohnson1990 

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))