Help with Dax

[AmandaHore]

I have a table that shows the sum of effort worked (Effortworked) on a project. 

Some of the effort worked is classified as written off (not billed to the customer).  

I want to show in a table view how much is the total effort worked (easy as it is a field in the table).  Then in another column the amount of that effort that is classified as 'written off'.  There is a field called OverrideType that contains the value of 'WrittenOff' or blank.  

If I apply to the filter it filters the whole table.  How would I write a measure that would show me:

If the project has time classified as 'written off' sum the values and display it in the table.  If nothing is classified as written off leave the value blank or show 0.  

All this is in the same table.   have tried to use the filter and related functions but not getting anywhere.  

Example of what I want it to look like.  

Project Number	Effort Worked	Written Off Amount
P1234	4,340	0
P4567	16,035	450
P7890	5452	0

[Anonymous · Accepted solution]

Hi @AmandaHore ,

Thanks for all the reply!
And @AmandaHore , here is my sample data:

You can use this DAXs to create two measures:

Total Effort Worked = 
CALCULATE(
    SUM('Table'[Effortworked]),
    ALL('Table'),
    'Table'[Project Number] = MAX('Table'[Project Number])
)

Written Off Amount = 
CALCULATE(
    SUM('Table'[Effortworked]),
    ALL('Table'),
    'Table'[OverrideType] = "WrittenOff" && 'Table'[Project Number] = MAX('Table'[Project Number])
)

This will cancel the effect of all possible filters (including filters and slicers) on the results. But I saw that you mentioned this sentence "If I apply to the filter it filters the whole table". If you have other filters or slicers that may be used, you need to modify these DAXs according to the fields involved. If possible, please tell me which fields you will apply to filter.

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

[ryan_mayu]

could you pls provide some sample data(not the table visual) and expected output?

[AmandaHore]

Thanks for the detailed replies.   These did not work as expected. 

The Effort worked is a sum of all effort on a project.  When I added in the new dax instead of being a rolled up sum it created a line for each day that time was recorded against the project.  I will go back to the drawing board on this one.  

[rajendraongole1]

Hi @AmandaHore - you have to create the two DAX measures

This measure calculates the total effort worked without any filters.

Eg: 

Total Effort Worked = SUM(EffortTable[EffortWorked])

This measure calculates the total effort worked but only for rows where the OverrideType is "WrittenOff." If no effort is classified as "WrittenOff," it will return 0.

another measure:

Written Off Amount =
IF(
ISBLANK(
SUMX(
FILTER(
EffortTable,
EffortTable[OverrideType] = "WrittenOff"
),
EffortTable[EffortWorked]
)
),
0,
SUMX(
FILTER(
EffortTable,
EffortTable[OverrideType] = "WrittenOff"
),
EffortTable[EffortWorked]
)
)

Hope this works.

[MFelix]

Hi @AmandaHore ,

If your field written off is part of your table has you refer then you should be able to use a measure similar to this one:

Written off = CALCULATE(SUM(Table[EfforWorked]),Table[Written off] = "Written Off" )+0