Table Wrong Totals

[Anonymous]

Hi All,

I have one column and two DAX measures to try to calcualte total number of flights. The rule is if the column [mode] equals "Grants" then (sum the tonnes and divde by 44) else 1.

Correct answers are Blocks 23 and Grants 418 (417 rounded is fine). 

The calcuated column "Flight col" has the correct blocks but incorrect grants.

Measure Flights has correct blocks 23 and grants 417 but total is wrong.

Measure Flight 3 has correct grants and total but blocks should be 23.

Below is the syntax for the three columns.

Flights col = IF('Append1'[Mode] = "Grants",
'Append1'[Committed Tonnes] / 44,
1
)

Flights = SWITCH(
    SELECTEDVALUE(Append1[Mode])
    ,"Grants",SUMX(Append1,[New Cmt_Tons]/44)
    ,SUM(Append1[Days])
)

Flight 3 = 
VAR sumtable = ADDCOLUMNS(SUMMARIZE(Append1
                           ,Append1[Mode]
                           ,Append1[New Cmt_Tons]),
                           "cmt_Tons",
                           CALCULATE(sum(Append1[New Cmt_Tons])),
                           "block",
                           CALCULATE(SUM(Append1[Days]),FILTER(Append1,Append1[Mode]<>"Grants"))
)
RETURN
SUMx(sumtable, IF(Append1[Mode] = "Grants",[cmt_Tons]/44,[block]) 

I'm close but its not quite right, thanks for any assistance. 

Cheers

[Anonymous]

There's only two references, one is a alias for the table colum name.

Sample Data (there doesn't seem to be a way to attach csv or zips)

See screen shot of data.

Qatar Data

I have fixed it by changing the column reference in the original calcuated column. But If anyone can tell me why Flight col works but Flights 3 doesn't?

New_Committed_Tonnes is a caluated column:

New Cmt_Tons = Append1[Committed Tonnes]/Append1[Days]

Works:

Flights col = IF('Append1'[Mode] = "Grants",

'Append1'[New Cmt_Tons] / 44,

1

)

Measure doesn't work:

Flight 3 =

VAR sumtable = ADDCOLUMNS(SUMMARIZE(Append1

,Append1[Mode]

,Append1[New Cmt_Tons]),

"cmt_Tons",

CALCULATE(sum(Append1[New Cmt_Tons]))

)

RETURN

SUMx(sumtable, IF(Append1[Mode] = "Grants",[cmt_Tons]/44,1)

Thanks

[lbendlin]

You show three different fields for committed tons, and none of their data. Hard to tell without that.