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

[binayjethwa]

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 no	Name	category 1	category 2	category 3	category 4	Overall Category
222	Binay	21	22	23	24	Cat1+ cat3+ Cat5
223	Binay1	22	23	24	25	Cat1+ cat3+ Cat5
224	Binay2	23	24	25	26	Cat1+ cat3+ Cat5
225	Binay3	24	25	26	27	Cat1+ cat3+ Cat5
226	Binay4	25	26	27	28	Cat1+ cat3+ Cat5
227	Binay5	26	27	28	29	Cat1+ cat3+ Cat5

[binayjethwa]

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.

[Ahmedx]

Is this what you are looking for?

[Ahmedx]

what is cat5 ?

[JoeBarry]

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]

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.

[Ahmedx · Accepted solution]

I do not fully understand what you need, but look at my file, it can help you

https://1drv.ms/u/s!AiUZ0Ws7G26RihuGvbEUbCo130dA?e=hybDhb

[binayjethwa]

Hi @Ahmedx , 

Thanks for the technique , it worked  but now the problem is the sorting order.

We used Category = Union(distinct(cagegory(category)) , {"Overall","annual sales",Sales % CY})

now i want to sorrt the columns like , cat 1 - cat 4 should be first , then ,overall sales, annual sales , sales % etc. 

what i did i created a separate colum in same table called sort column  , and try to sort it using this sort column. 

When i try to sort this i am getting circular dependency . is there any way we can fix this .

THanks in advance

Sort column = switch(true(), 

category ="cat 1",1,

category ="cat 2",1,

.

.

.

category ="cat 5",5,

category = "Overall sales", 6 etc

[Ahmedx]

pls try this

dumpCategory = ADDCOLUMNS( 
    UNION(DISTINCT('Table'[cat]),{"Overall Category"}), "Order", 
SWITCH( TRUE() , 
        [cat]= "Category 1",1,
        [cat]= "Category 2",2,
        [cat]= "Category 3",3,
        [cat]= "Category 4",4,
        [cat]= "Overall Category",5
))

[binayjethwa]

Hi @Ahmedx , I tried usint below measure ,

For table , the values are not getting sorted correctly in power bi , and getting blank values.

For Measure : I am getting Circular dependency. 

Please advice.

Table :

Em category =

ADDCOLUMNS(

    UNION(

        DISTINCT(HC_Master[EM Comments Category]),

       {"Bench %","Bench Count","Billable %","Grand Total"})

       ,"Order",

SWITCH(TRUE(),

HC_Master[EM Comments Category] ="Bench",1,

HC_Master[EM Comments Category] ="Billable",2,

HC_Master[EM Comments Category] ="Blocked",3,

HC_Master[EM Comments Category] ="Proposed",4,

HC_Master[EM Comments Category] ="Long Leave - ML",5,

HC_Master[EM Comments Category] ="PPED",6,

HC_Master[EM Comments Category] ="Grand Total",7,

HC_Master[EM Comments Category] ="Bench Count",8,

HC_Master[EM Comments Category] ="Bench %",9,10

//HC_Master[EM Comments Category] ="Billable %",10

))

Measure

Sorting = SWITCH(TRUE(),

'Em category'[EM Comments Category] ="Bench",1,

'Em category'[EM Comments Category] ="Billable",2,

'Em category'[EM Comments Category] ="Blocked",3,

'Em category'[EM Comments Category] ="Proposed",4,

'Em category'[EM Comments Category] ="Long Leave - ML",5,

'Em category'[EM Comments Category] ="PPED",6,

'Em category'[EM Comments Category] ="Grand Total",7,

'Em category'[EM Comments Category] ="Bench Count",8,

'Em category'[EM Comments Category] ="Bench %",9,10

//'Em category'[EM Comments Category] ="Billable %",10

)

[Ahmedx]

Share sample pbix file to help you.

[binayjethwa]

Hi @Ahmedx ,

Here when you say [Cat] does it mean category from master table , and if it is from master table how will i call overall category since it will not be present in Master table. becuase we created in dump . so how to call overall category here.  the below step since its not present in master table.

 [cat]= "Overall Category",5

[Ahmedx]

this is not a master table but with a table I created 

ADDCOLUMNS( 

[binayjethwa]

Hi @Ahmedx ,

This is working now , sort column is created and when i do a sort by column on category using sort , i see some values are missed out in my table.

before sort i see all the values in the table , after sort i dont see Bench count column and Billable % and Bench % are coming as blank and if i revert the sort , i see all values, not sure whats causing the issue. Please help here

Below are my measures: 

Em category =

ADDCOLUMNS(

    UNION(

        DISTINCT(HC_Master[EM Comments Category]),

       {"Bench %","Bench Count","Billable %","Grand Total"})

       ,"Order",

SWITCH(TRUE(),

HC_Master[EM Comments Category] ="Bench",1,

HC_Master[EM Comments Category] ="Billable",2,

HC_Master[EM Comments Category] ="Blocked",3,

HC_Master[EM Comments Category] ="Proposed",4,

HC_Master[EM Comments Category] ="Long Leave - ML",5,

HC_Master[EM Comments Category] ="PPED",6,

HC_Master[EM Comments Category] ="Grand Total",7,

HC_Master[EM Comments Category] ="Bench Count",8,

HC_Master[EM Comments Category] ="Bench %",9,10

//HC_Master[EM Comments Category] ="Billable %",10

))

Bench count =

CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),'Em category'[EM Comments Category] IN {"Bench","Blocked","Proposed"})

Bench % =

VAR total  = CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),ALL('Em category'))

VAR bench =  CALCULATE([Bench count])

return

DIVIDE(bench,total)

billable % =

VAR total  = CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),ALL('Em category'))

VAR Billable =  CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),'Em category'[EM Comments Category]="Billable")

return

DIVIDE(Billable,total)

[binayjethwa]

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

[Ahmedx]

Share sample pbix file to help you.

show measure [Bench %]

[binayjethwa]

Hi @Ahmedx ,

i think i was using filter from master table instead of category table. THis is working now , But i need to add multiple values so i added as below.

category = UNION(DISTINCT(Master[Comments Category]),{"Bench %","Bench count","Billable %"})

 I have separate measures created for Bench %  , Billable % etc

and i used switch in overall measure and if selected = Bench % retuen Bench % measure  selectedvalue = Billable % return Billable % and so on , and i changed any filters coming from category table only from master table. But it is only working for one values.

please let me know if i am missing anything here

My overall measure=

VAR benchcount = [Bench count]

VAR overallcount = DISTINCTCOUNT(HC_Master[GPN])

VAR billablecount = CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),'Em category'[EM Comments Category]="Billable")

return

SWITCH(TRUE(),

SELECTEDVALUE('Em category'[EM Comments Category]) = "Bench Count",[Bench count],

SELECTEDVALUE('Em category'[EM Comments Category]) = "Bench %",DIVIDE(benchcount,overallcount),

SELECTEDVALUE('Em category'[EM Comments Category]) = "Billable %",DIVIDE(billablecount,overallcount),DISTINCTCOUNT(HC_Master[GPN]))

[JoeBarry]

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]

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.

[JoeBarry]

Good morning

this measure could be a basis for counting a specific category.

CALCULATE(COUNT(Table[Categories], KEEPFILTERS([Categories] = "category 1"))

If you need more than one category change the above measure to 

KEEPFILTERS In {"category 1", "category 2"}))