Forum Discussion
Avoid repeating values for multiple fields placed in matrix visual in Values section.
I do not fully understand what you need, but look at my file, it can help you
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.
I do not fully understand what you need, but look at my file, it can help you
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,BinayShare sample pbix file to help you.
show measure [Bench %]
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")returnSWITCH(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]))
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
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 ))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