Forum Discussion

shwets47's avatar
shwets47
Frequent Visitor
9 years ago
Solved

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 ...
Need Help
  • shwets47's avatar
    shwets47
    9 years ago

    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_fees

     

    and 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_fees

     

    and got below:

     

    FactorClassSubclassFee ReceivedModalityManagerScore
    0.000411251000UniformSunnyvale19
    0.000512260000UniformSunnyvale17
    0.000513270000UniformSunnyvale10
    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

  • shwets47's avatar
    shwets47
    9 years ago

    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

mattbrice's avatar
mattbrice
Icon for Solution Sage rankSolution Sage
9 years ago

as a calculated column:

 

measure =
VAR all_fees =
     SUM ( Table[Fee Received] )
RETURN
    Table[Factor] * all_fees

as 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_fees
  • shwets47's avatar
    shwets47
    Frequent Visitor
    9 years ago

    Hi 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's avatar
      mattbrice
      Icon for Solution Sage rankSolution Sage
      9 years ago

      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
      • shwets47's avatar
        shwets47
        Frequent Visitor
        9 years ago

        oh ok i renamed the variable, but now it just gives me 

        19
        17
        10

         still doesnt give me the exact number :( :( :( which is 72.4 for classSubclass 112 ,181000*0.0004 = 72.4

         

        Regards,

        Shweta