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 ...
Need Help
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 JoeBarry ,

         

        The problem is i need to add distinct count of emp no and Count for certain category in matrix , so whether 2 measures or columns if i place both fields in values selection , it will still have repeating values.

         

        I was specifically saying something like this as below image. I dont want repeating values for count of emp no  and count of certain category for all categories.

         

         

      • binayjethwa's avatar
        binayjethwa
        Helper V
        3 years ago

        Hi Ahmedx ,

         

        i tried the similar way by creating a table for category and mapping with my category of actual table and create measure , but its not working for me.

        Created a table with this:

        category = UNION(DISTINCT(Master[Comments Category]),{"Bench %"})
        Used below measure : 
        IF(SELECTEDVALUE('category'[Comments Category]) = "Bench %",[Bench %],DISTINCTCOUNT(emp no ))
         
        i see values for other categories but bench % is not showing althiugh the values used to calculate for it is used from master table.
         
        there is a one to many relation created between category(comments category) to master(Comments category).
         
        Thanks,
        Binay