Forum Discussion
Need help in row level context in DAX
Hi mattbrice,
i pasted this:
My Health Score =
VAR all_fees =
Calculate( Sum(Remittance[Amount paid]), All(Remittance))
VAR this_factor =
SELECTEDVALUE ( Factor[Factor] )
RETURN
this_factor * all_feesand i got duplicates(couldnt attach a screenshot!), but it was definitely giving me wrong values , hence i did this:
My Health Score =
VAR all_fees =
Sum(Remittance[Amount paid])
VAR this_factor =
SELECTEDVALUE ( Factor[Factor] )
RETURN
this_factor * all_feesand got below:
Factor ClassSubclass Fee Received Modality Manager Score 0.0004 112 51000 Uniform Sunnyvale 19 0.0005 122 60000 Uniform Sunnyvale 17 0.0005 132 70000 Uniform Sunnyvale 10 0.0014 1810000 which is still not satisfying my need. i need 181000*0.0004 = 72.4 for subclass 112 and so on. thsi should be done dynamically on row level.
My current formula which is SUMX ( Factor, [Fee received] * Factor[Factor] ) is just multiplying the respective row to corresponding row, i.e 51000*0.0004 and 60000*0.0005 and so on. but i want a sum of the fee received for that particular modality and manager and multiply it by the corresponding factor of the subclass.
Regards,
Shweta
Hi All,
Thank you all for your help. Looks like my client was unclear as to what he wanted. As of now my formula is validated and looks correct , hence i am just closing this topic. Thanks again for all the help!
Regards,
Shweta
Microsoft EmployeeHi shwets47,
Your get Fee received from different tables, for example Fee received1= SUM(Table1[Amount paid]),Fee received2= SUM(Table2[Amount paid]), or Fee received3= SUM(Table3[Amount paid]).
Please try the formula and check if it works fine.
result=(SUM(Table1[Amount paid])+SUM(Table2[Amount paid])+SUM(Table3[Amount paid]))*Factor[factor]
Do you mind share your .pbix file for further analysis? It's hard to reproduce your scenario, so you'd better share more details.
Best Regards,
Angelia
Hi Angelia,
Thank you for your reply. But your formula wouldnt work for me for the same reason as Tom's, it says
"A single value for column 'Factor' in table 'Factor' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
but i need a single value from the column Factor multiplying with, sum of the amount paid. I know there is something that is very simple and I am missing it!
Sorry i wouldnt be able to share the pbix file as its a confidential client data and i cannot share it with anyone.
Regards,
Shweta