Forum Discussion
Need help in row level context in DAX
Hi Guys,
I have a below table :
| 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 |
I have calculated the score like this: SUMX ( Factor, [Fee received] * [Factor] ) , but what i actually want is sum of Fee Received multiplied by the factor of that ClassSubclass, Modality and Manager. i.e. 51000+60000+70000 = 181000 * 0.0004 = 72.4.
How do I achieve this?
Regards,
Shweta
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
12 Replies
- TomMartens
Super User
Hey,
you can create a calculated column like so
Score CalcColumn = SUM('Table2'[Fee Received]) *'Table2'[Factor]This returns the following values
The above DAX statement is based on my current knowledge about your datamodel.
Regards
Tom
Hi Tom,
Thank you for your reply. But my Fee received is actually already a measure calculated from different table as sum of amount paid. Hence that is not possible . So i need to use SUMX but on a particular classSubclass level.
My Fee received formula is
Fee received = SUM(Remittance[Amount paid]), but even if i apply that i cannot simply take single row value for Factor , it should be sum or aggregate. say if i do this according to your logic:
Sum(Remittance[Amount Paid]) * [Factor], it says below:
"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."
I am not sure how to achieve it!
Regards,
Shweta
mattbriceSolution Sage
as a calculated column:
measure = VAR all_fees = SUM ( Table[Fee Received] ) RETURN Table[Factor] * all_feesas a measure, it depends on what other rows,columns, filters, slicers, etc are being applied, but something like this:
measure = VAR all_fees = CALCULATE ( SUM ( Table[Fee Received] ), ALL ( Table ) ) VAR factor = SELECTEDVALUE ( Table[Factor] ) RETURN factor * all_feesHi Mattbrice,
Thanks for the formula, i tried and not working, it says:
'factor' is a table name and cannot be used to define a variable.
Please help! i am new to DAX and finding it difficult to achieve this particular formula!
Regards,
Shweta
- mattbrice
means you need to change the name of the variable i declared as it conflicts with a table name in your model.
measure = VAR all_fees = CALCULATE ( SUM ( Table[Fee Received] ), ALL ( Table ) ) VAR this_factor = SELECTEDVALUE ( Table[Factor] ) RETURN this_factor * all_fees
