Forum Discussion

binayjethwa's avatar
binayjethwa
Helper V
3 years ago
Solved

Avoid repeating values for multiple fields placed in matrix visual in Values section.

Hi ,

 

I have the following requirement , i have below table which i am showing in a matrrix visual .

Rows: Emp no and name

Columns : Category 

values : total count 

 

Now i need to calculate overall category which is cat1+ cat3+Cat 5 , but if i create this measure /column and place in values section in matrix , it gets repeated for each category. But i want to display

only as single column like overall category.

 

Emp noNamecategory 1category 2category 3category 4Overall Category
222Binay21222324Cat1+ cat3+ Cat5
223Binay122232425Cat1+ cat3+ Cat5
224Binay223242526Cat1+ cat3+ Cat5
225Binay324252627Cat1+ cat3+ Cat5
226Binay425262728Cat1+ cat3+ Cat5
227Binay526272829Cat1+ cat3+ Cat5
Need Help

19 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    3 years ago

    Hi binayjethwa 

     

    Is the table from where this data os coming from Tabular format ( The Cat column names are in one column and the Values have their own columns) or as Pivot (Cat 1 Cat 2 etc. have their own columns?

     

    Do you want a text result as in the screenshot or a sum of the values?

     

    Thanks

    Joe

     

    • binayjethwa's avatar
      binayjethwa
      Helper V
      3 years ago

      Hi JoeBarry 

       

      Here the values coming in Cat 1, cat 2 ... cat 5 are just a count of emp no ,  so in values section i have only count of emp no , now to have over all then i need to add another column or measure created for overall category and add in values section , now since it has Count of emp no and overall category in values , the table will show values for all categories twice. which i do not need. Is there any way we can achieve this.

       

      here i need to create a measure or column for overall category and place it in values section below which will make matrix have repeatitive values of Count of emp no and overall category for all the categories.

       

       

       

      • JoeBarry's avatar
        JoeBarry
        Solution Sage
        3 years ago

        Hi binayjethwa 

         

        I think Ahmedx  had the solution for you. above.

         

        First of all, always create measures for values. They can be reused later in other visuals and can be absis for further measures.

         

        1. Create a base Employee Count Measure. this should be enough if using a Matrix. Keep your matrix as is a replace the count with this measure. The Matrix will also show you a total for all categories

        Total Employees = DISTINCTCOUNT(Table[Emp no])

        2. If you need to show certain categories only

        Overall categories = CALCULATE([Total Employees], KEEPFILTERS(Table[Categories] in {"category 1", "category 3", "category 5"})

         

        Thanks

        Joe

  • binayjethwa's avatar
    binayjethwa
    Helper V
    3 years ago

    Hi Ahmedx ,

     

    Here the values coming in Cat 1, cat 2 ... cat 5 are just a count of emp no ,  so in values section i have only count of emp no , now to have over all then i need to add another column or measure created for overall category and add in values section , now since it has Count of emp no and overall category in values , the table will show values for all categories twice. which i do not need. Is there any way we can achieve this.

     

    here i need to create a measure or column for overall category and place it in values section below which will make matrix have repeatitive values of Count of emp no and overall category for all the categories.