Forum Discussion

twister8889's avatar
twister8889
Icon for Helper V rankHelper V
5 years ago
Solved

averagex and Allexcept

Hi,

 

I'm trying to create an average by type using the allexcept. The measure was working fine until I had the same types in different categories.

 

This is my measure:
CALCULATE (AVERAGEX(Table2;Values);ALLEXCEPT(Table2;Table2[Type])

I have slicers to Table 1 (Category) and another slicer (Type) to Table 2.

 

 

So my problem is:
- When I use the slicer Category, with the last category (L) the result is not only to Category L but L and I because I have the same Type R for both. 
- When I use the slicer Category L and Type = R, I have average wrong, because is considering all Types R in Category L and I, 

 

how can do I do to measure consider the only Type for each Category?

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    twister8889 

     

    Maybe try adding a condition that category must be the same, it's very late here so my brain is fuzzy, but see if this works: 

     

    Measure = 

    VAR _Cat = SELECTEDVALUE(Table2[Category])

    CALCULATE (AVERAGEX(FILTER(Table2, Table2[Category]= _Cat;Values);ALLEXCEPT(Table2;Table2[Type])

  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi twister8889 ,

    You can modify your measure like this:

    Average = 
CALCULATE (
    AVERAGE ( 'Table2'[Values] ),
    FILTER (
        Table2,
        'Table2'[Category]
            IN DISTINCT ( 'Table1'[Category] )
                && 'Table2'[Type] IN DISTINCT ( 'Table2'[Type] )
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    twister8889 

     

    Maybe try adding a condition that category must be the same, it's very late here so my brain is fuzzy, but see if this works: 

     

    Measure = 

    VAR _Cat = SELECTEDVALUE(Table2[Category])

    CALCULATE (AVERAGEX(FILTER(Table2, Table2[Category]= _Cat;Values);ALLEXCEPT(Table2;Table2[Type])

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    twister8889 , Try a measure like

    CALCULATE (AVERAGE(Table2[Values]);filter(allselected(Table2);Table2[Type] =max(Table2[Type])))

    • twister8889's avatar
      twister8889
      Icon for Helper V rankHelper V
      5 years ago

      First of all, thank you for your answer

       

      I inserted the measure, but I have the same result:

       

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi twister8889 ,

    You can modify your measure like this:

    Average = 
CALCULATE (
    AVERAGE ( 'Table2'[Values] ),
    FILTER (
        Table2,
        'Table2'[Category]
            IN DISTINCT ( 'Table1'[Category] )
                && 'Table2'[Type] IN DISTINCT ( 'Table2'[Type] )
    )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.