Forum Discussion

ssutton's avatar
ssutton
Frequent Visitor
7 years ago
Solved

Calculating Normalized Weighted Value

The data looks similar to this:

ABCDEF
A1.8659.313.56.89
A00013.50.00
A02013.50.00
B2.451213.58.89
B00013.50.00
B02013.50.00
C2.5512.513.59.26
C0.110113.50.74
C02013.50.00

 

Column E represents the Max Sum Value which is Line C 12.5 + Line C 1 = 13.5

Goal 1: Create Dax Expression that caluclates the Max Sum by Line (Column A)

Goal 2: New measure to calculate Sum by Line divided by Max Sum Value multiplied by 10.

The end result is a normalized weighted value by Line A, Line B, Line C

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I hope that I understood this correctly

    Max Sum Value = 
MAXX(
    SUMMARIZE(
        ALL('Table');
        'Table'[A];
        "Total";SUM('Table'[D])
    );
    [Total]
)

    and

     

    Weighted Sum Value = 
DIVIDE(SUM('Table'[D]);[Max Sum Value])*10

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I hope that I understood this correctly

    Max Sum Value = 
MAXX(
    SUMMARIZE(
        ALL('Table');
        'Table'[A];
        "Total";SUM('Table'[D])
    );
    [Total]
)

    and

     

    Weighted Sum Value = 
DIVIDE(SUM('Table'[D]);[Max Sum Value])*10
    • ssutton's avatar
      ssutton
      Frequent Visitor
      7 years ago

      Thank you for Anonymous.  I can somewhat follow the expression, but not understanding where the "Total" comes from in the expression.  Currently I am getting error that "Too few arguments were passed to the SUMMARIZE function.  The minimum argument count for the function is 2."

       My expression lools like this:

      Max Sum Value = MAXX(SUMMARIZE(ALL(Concept,Concept[Route_A],"Total",SUM(Concept[D]),[Total])))

       

      Forgive my ignorance- this is new for me.  Is this meant to be in a column or a dax expression in a new table?
      Thank you
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        This should be a measure, not columns. And your ")" was in wrong places (in bold). The SUMMARIZE creates the column "Total". And once you have created the Total column, we take the Max value in that column.

         

        Max Sum Value = 
MAXX(
  SUMMARIZE(
    ALL(Concept),
    Concept[Route_A],
    "Total",SUM(Concept[D])
   ),
   [Total]
)


        To see how the summerize function work, press the "New Table" at the top and copy this into the function:

        Table = 
SUMMARIZE(
    ALL(Concept),
    Concept[Route_A],
    "Total",SUM(Concept[D])
   )



        Please mark this as solved and give tumps up if it solves your problem.