Forum Discussion
Solved
Filtered Sum in calculated column
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 |
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])ReturnCALCULATE(SUM(Invoice_Table_Master[Patient Count on Inv.]),Invoice_Table_Master[Market] = Market1,Invoice_Table_Master[Month] = Month1,ALL(Invoice_Table_Master))
3 Replies
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)] )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?
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])ReturnCALCULATE(SUM(Invoice_Table_Master[Patient Count on Inv.]),Invoice_Table_Master[Market] = Market1,Invoice_Table_Master[Month] = Month1,ALL(Invoice_Table_Master))
