Search code examples
excelpowerbipowerpivotcalculation

Calculate monthly efficiency in power pivot

I am very new to this power pivot and dax Commands ,kindly help me out here

my sample table looks like this : Sample Table

Now to calculate monthly efficiency i need to make a measure which satisfies below criteria:

Sum the efficiency for each distinct date & divide by the count of distinct dates for the month

so far for days my below formula is working , but when i group the date into month it is only showing the sum, so kindly help me how to achieve this ;

Actual Efficiency CL2:=CALCULATE(SUM(CL1[Day Wise Efficiency]),DISTINCT(CL1[Date (dd/mm/yy)]))

Day wise result

Monthly result showing the sum where i need the total sum divided count of distinct day in dates column for the month

Solution

  • Divide part is not in your formula, below DAX might help:

    Actual Efficiency CL2 :=
DIVIDE (
    CALCULATE ( SUM ( CL1[Day Wise Efficiency] ) ),
    CALCULATE ( DISTINCT ( CL1[Date (dd/mm/yy)] ) )
)

    Note that DISTINCT ( CL1[Date (dd/mm/yy)] ) is not required in the sum as dates in rows will be unique. To Divide the sum at month level DISTINCT ( CL1[Date (dd/mm/yy)] ) is required as we need how many days a month has. At day level there will always be 1 to divide by.

    Thanks