Filtered Sum in calculated column

[ilooper]

Hello all,

I have what i feel should be easy problem to solve but my DAX isn't working.  I have a table that I would like to add a column that returns the sum of a column filtered.  Can I do that?

Month	Region	Zone	Market	Customer	Orders (for month)	Total Orders (Market)	Ratio
Jan	Texas	Texas - North	Dallas	Bob's ER	5	5	1.0
Jan	Texas	Texas - South	Houston	Tom's ER	5	12	.42
Jan	Texas	Texas - South	Houston	First ER	7	12	.58
Feb	Texas	Texas - North	Dallas	Bob's ER	8	10	.8
Feb	Texas	Texas - North	Dallas	Nice ER	2	10	.8
Feb	Texas	Texas - South	Houston	First ER	5	5	1.0

[ilooper · Accepted solution]

I figured it out through measure.  The only problem is I can't (yet) roll up through the hierachy becasue of the filter context.

Total Patients (Market/Month) =

Var Market1 = SELECTEDVALUE(Invoice_Table_Master[Market])

Var Month1 = SELECTEDVALUE(Invoice_Table_Master[Month])

Return

CALCULATE(SUM(Invoice_Table_Master[Patient Count on Inv.]),

Invoice_Table_Master[Market] = Market1,

Invoice_Table_Master[Month] = Month1,

ALL(Invoice_Table_Master))

https://youtu.be/t7gj_iVTGPc

[ilooper]

Thanks for your help! It doesn't look like the dax for the Total Patients (Market) is working it is suming all.  What do you think? 

[jdbuchanan71]

@ilooper 

You can write the following measures to get your values.

Order Amount = SUM ( 'Table'[Orders (for month)] )

Total Orders (Market) = 
CALCULATE ( 
    [Order Amount],
    ALLEXCEPT ('Table','Table'[Month],'Table'[Market] )
)

Ratio = 
DIVIDE ( [Order Amount], [Total Orders (Market)] )