Forum Discussion

mschultens's avatar
mschultens
New Member
8 years ago
Solved

Calculate Average per category

Hello everyone,   I spent a lot of time already looking at similar problems to the one I have, but nothing really worked for me.  Assume I have a table with a category and some values. How can I c...
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi mschultens,

     

    Did you use the original expression in the calculated column? It works for me. Please refer:

     

     

    Or you can try measure:

    AverageMeasure =
CALCULATE (
    AVERAGE ( Table3[Value] ),
    FILTER ( ALLSELECTED ( Table3 ), Table3[Category] = MAX ( Table3[Category] ) )
)

    Thanks,
    Xi Jin.

mschultens's avatar
mschultens
New Member
8 years ago

It gives me an error saying that a single value for category cannot be determined. I tried putting a MAX() in front, but that gives the same error.

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi mschultens,

     

    Did you use the original expression in the calculated column? It works for me. Please refer:

     

     

    Or you can try measure:

    AverageMeasure =
CALCULATE (
    AVERAGE ( Table3[Value] ),
    FILTER ( ALLSELECTED ( Table3 ), Table3[Category] = MAX ( Table3[Category] ) )
)

    Thanks,
    Xi Jin.

    • jochendecraene's avatar
      jochendecraene
      Helper V
      4 years ago

      Hi

       

      I'd like to use this solution, but I also have a subcategory and year that I have to take into account.

      I tried using extra filters for subcategory and year, but keep getting errors.

      Can someone help me?

      This is what my table looks like

       

       

      • sabinozen's avatar
        sabinozen
        Frequent Visitor
        3 years ago

        I'm also intrested in this. Did you solved it?

    • mschultens's avatar
      mschultens
      New Member
      8 years ago

      I had a mistake somewhere else in my model. Both solutions work fine! Thank you very much guys! :-)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      v-xjiin-msft 

       

      I have a question about average too

       

      I have this data, what I want to achieve :

      > Average Total : points PR+WAR from all technician

      > Average PR & WAR : point only for PR OR only WAR based on FILTER

       

      This is my code but gave me value :

      YESTERDAY

      > Average Total : correct value 7,5 this DAX gave me 3,75  (WRONG)

      > Average PR & WAR : correct value PR = 3 and WAR = 4.5 this DAX gave me PR = 3 and WAR = 4.5 (CORRECT)

      Bassicly how to make this DAX give a correct value for Average Total and Average PR & WAR 

       

      1. AVG_Points = AVERAGE(CL_PROD[Points])

2 AVG_Points_Filter = IF(MAX(CL_PROD[WO_Type])= "PR" || MAX(CL_PROD[WO_Type])= "WAR",
	            [AVG_Points],[AVG_Points]*2)