Forum Discussion
New Measure with a cross filter
I am trying to build a new calculation, dividing a category Hours by Hours that are categorized by a separate table under Billable Status> Billable. To start this process, I tried creating a new measure, basically to create the numerator of billable hours. In the end I want to have billable hours/total hours as a percentage.
My DAX formula for the numerator would not allow me to use the filter billable status> is billable, only 'billable status' so my pitiful attempt ended with this:
Measure = CALCULATE(SUM('TIME TRACKING VALUES'[Hours]),FILTER('Transaction Line Attributes', 'Transaction Line Attributes'[Billable Status]= 'Transaction Line Attributes'[Billable Status], Billable)
I understand it is riddles with mistakes I need help with ideas on how to build this.
Is this what you need? the column 'Hours' is in the table TIME TRACKING VALUES and the other table is 'transaction Line Attributes' which includes the filter 'Billable Status' where the thre options are 'billable,' unbillable,' and 'billed.' i would like to have the hours categorized as 'billable' divided by all hours: billable, unbillable, and billed. I cannot share the actual columns with you unfortunately because the data is private. I hope this is enough. connection page. set up by QQube.Billable Status TableTime Tracking Values table
Thank You
4 Replies
- Greg_Deckler
Community Champion
Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Is this what you need? the column 'Hours' is in the table TIME TRACKING VALUES and the other table is 'transaction Line Attributes' which includes the filter 'Billable Status' where the thre options are 'billable,' unbillable,' and 'billed.' i would like to have the hours categorized as 'billable' divided by all hours: billable, unbillable, and billed. I cannot share the actual columns with you unfortunately because the data is private. I hope this is enough. connection page. set up by QQube.Billable Status TableTime Tracking Values table
Thank You
The connection between Hours and their billing status seems to be based on that linktotxnID which was created by the QQube Quickbooks connector.
Measure = CALCULATE(SUM(('TIME TRACKING VALUES'[LinkToTxnID], FILTER('Transaction Line Attributes'[Billable Status])))/SUM('TIME TRACKING VALUES'[Hours])
this is my most recent attempt. It says the syntax for 'Measure' is incorrect
