Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Measures Filtered By Dimensions

This has probably been answered but im not sure what this is called so I'm not finding it. I essentialy want to create measures filtered on dimension values (I think, im sure there is a better way than what I did below).

 

I have 2 Tables (Costs & Cost Types)

Costs:  (Amount, CostTypeID)
Cost Types: (CostTypeID, CostTypeDesc)

 

Out of the box I'm able to display all amounts by cost type by dragging cost type and amount onto my matrix

 

But in my case I'd like to display the cost type as a column BUT I'd also like to pick and choose which cost types to display. Based on the structure above when I move cost types to the columns it displays every cost type in the cost type table. However, I don't want to allways display all the costs types and would rather pick and choose which costs are showing up.

 

In order to do this I did the following which I don't exactly like because it depends on me filtering by text. I created a new measure and then entered the following formula

 

I've create a measure for each cost type in my table so that I'm able to pick and choose which costs to display. In this image below I'm showing the Estimated Costs and Estimated material measure.

 

Base on what you've seen is this the correct way to do this? What other options are available?

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    For what its worth I believe this is called creating a Set in Tableau. Essentially I create a set in tableau and name it Estimated Labor and then include the cost type 'Estimated Labor' as a filter'. I suppose its no different than what I did in the function, however, tableau lets you select the values to include rather than typing them out.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi lmf232s,

     

    According to your description, you want to display the specify type which you choose? If as I said, you can use slicer to achieve your requirement.

     

    In addition, if you want to use measure to do the same work, you can refer to below code:

     

    Measure(sum of the same type):

Sum of Amount = CALCULATE(sum(Costs[Amount]),FILTER(ALL(Costs), SUMX(FILTER(Costs,Costs[CostTypeID]=EARLIER(Costs[CostTypeID])),Costs[Amount])))

     

     

    Table2 (show the specify data):

Table= DISTINCT( SELECTCOLUMNS(Costs,"Type",RELATED('Cost Types'[CostTypeDesc]),"Amount", if(Costs[CostTypeID]=LOOKUPVALUE('Cost Types'[CosCostTypeID],'Cost Types'[CostTypeDesc],"Estimatedmaterial")||Costs[CostTypeID]=LOOKUPVALUE('Cost Types'[CosCostTypeID],'Cost Types'[CostTypeDesc],"EstimatedLabor"), [Sum of Amount],BLANK())))

     

     

     

     

     

    Regards,

    Xiaoxin Sheng