Forum Discussion

AmandaHore's avatar
AmandaHore
Helper I
1 year ago
Solved

Help with Dax

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 vie...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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.