Forum Discussion
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 ...
I do not fully understand what you need, but look at my file, it can help you
this is not a master table but with a table I created
ADDCOLUMNS(
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)