Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago
Solved

DAX Summarize By Employee With Formula

I have a table like this that computes the Quota Impact by this formula.  ([Quota]/([Days in Month]*8))*[Hours Closed].  It works fine when all the rows of the table are selected in the Visual.  Howe...
COUNT
Filter
SUMMARIZE
  • aeagleton-coop's avatar
    aeagleton-coop
    1 year ago
    Quota Impact =

    VAR Granularity =
    SUMMARIZE(
        InputTable,
        InputTable[Emp],
        InputTable[Location],
        InputTable[Date],
        InputTable[Quota]
    )

    RETURN
    SUMX(
        Granularity,
        VAR HoursClosed = CALCULATE(SUM(InputTable[Hours Closed]))
        VAR DaysInMonth = 31  /* DAY(EOMONTH([Date], 0)) */
        RETURN [Quota] / (DaysInMonth * 8) * HoursClosed    
    )
aeagleton-coop's avatar
aeagleton-coop
New Member
1 year ago
Quota Impact =

VAR Granularity =
SUMMARIZE(
    InputTable,
    InputTable[Emp],
    InputTable[Location],
    InputTable[Date],
    InputTable[Quota]
)

RETURN
SUMX(
    Granularity,
    VAR HoursClosed = CALCULATE(SUM(InputTable[Hours Closed]))
    VAR DaysInMonth = 31  /* DAY(EOMONTH([Date], 0)) */
    RETURN [Quota] / (DaysInMonth * 8) * HoursClosed    
)