Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

how to add average column for matrix visual

how to add avarage coulmn for matrix visual, I want one extra coumn and with avargae values of six months 

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Apply the measure and rename column subtotal

    Measure = IF(ISINSCOPE('Table'[Month]),SUM('Table'[Value]),AVERAGE('Table'[Value]))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Apply the measure and rename column subtotal

    Measure = IF(ISINSCOPE('Table'[Month]),SUM('Table'[Value]),AVERAGE('Table'[Value]))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you so much for your help!

    Can you please help me how to add the minimum and maxmum values for the same 

    • v-jianboli-msft's avatar
      v-jianboli-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi Anonymous ,

       

      If you need more than one column, you should change your data model.

      Here  is an example:

      First create a new table like this:

      Then manage relationship:

      Then apply the measure:

      Measure = 
SWITCH(TRUE(),
SELECTEDVALUE('Table 2'[Month]) in ALL('Table'[Month]),SUM('Table'[Value]),
SELECTEDVALUE('Table 2'[Month])="Maxmum",MAXX(ALLEXCEPT('Table','Table'[ID]),[Value]),
SELECTEDVALUE('Table 2'[Month])="Minimum",MINX(ALLEXCEPT('Table','Table'[ID]),[Value]))

      Final output:

       

       

      Best Regards,

      Jianbo Li

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.