How to get my measure to slice each value in this table?

[telusive]

Currently, I have a measure that takes Billable Hours (from a calculated column) and divides it by full time employee hours. Normal thing, 20/40 40/40 etc. However, in my table, I want to be able to have a slicer that says if under 50%, show, if not, dont show.

Currently, my slicer is only applying to the total Billable%. How do I get it to apply to all the values?

Ideally I'd like to remove the blue in the table. I believe the pink in the table is the only thing the logic is looking at. 

Thanks in advance!

[v-stephen-msft · Accepted solution]

Hi @telusive ,

Here's my workaround, you just create a new measure to replace the original one.

Measure = IF([Billable%]<=0.5,[Billable%],BLANK())

As you can see, my newly created measure, values greater than 50% are blank.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arava]

It may be memory-intensive depending on your dataset size, but creating a new Billable% calculated column that controls for [Date] and [Name] would create something that you can use as an effective filter. Something like the below should work:

calculatedBillable%Column = CALCULATE([Billable Hours] / [FTE Hours], FILTER(myTable, EARLIER([Date]) = [Date] && EARLIER([Name]) = [Name]))

[telusive]

I had

calculatedBillable%Column =

CALCULATE(

    SUM('Master List'[Billable]) / SUM('Static Hours'[AVALIABLE FTE]),

    FILTER(

        'Master List',

        MAX('Static Hours'[Dates]) = MAX('Static Hours'[Dates]) &&

        'Master List'[Name] = EARLIER('Master List'[Name])

    )

)

but the results are strange:

Is this bc its a matrix table and there is 2 halves or did I screw up the calculated column? I had to reference the calculated columns with the MAX functions, did that cause the issues?

[arava]

You should be using EARLIER( ) for both, and EARLIER( ) should come first in the formula (ie, EARLIER([column]) = [column] )

And just as an FYI, this kinda of filtering will only show values of 31% and not data for the entire row.

[telusive]

In theory, then I could group all values under 50% or is what I'm asking not possible?

[arava]

Absolutely! You can just take the previous code and make it a variable (lets call it [temp] here) and use it as below:

yourColumn = 

var temp = [...code from previous step...]

return if(temp <0.5, "Under 50%", "Not Under 50%")