Sumif equivalent for Power Pivot Measure Calculations

Question

this is my first question here and i will try my best to ask my question in a good way.

I am very experienced in using Excel, but quiet new to the Power Tools. I have a Data Model set up in the following way

Table SalesData:

Day | Amount Ordered | Amount Sent | toBeAchived (Service Level per Month)* 
1          10              10                     87,5%
2          20              15                     87,5%
3          15              15                     87,5%
..
51         20              18                     90,0%

Table Calender:

Day | Month
1       1
2       1
3       1
..      
51      2

I then created a measure: Service Level:= sum(Amount Ordered) / sum ( Amount Sent)

If I now create a pivot table I can aggregate the month and see the service level of for example month 1.

Now I want to build a graph, with days on the x-Axis, but with the Mothly Average Service Level, but since its disaggregated I get the measure daily.

so what I need is a sumif(Amount ordered, Month = @Month) which would be easy in an Excel Table.

*I tried the Calculate function like this as a CalculatedColumn: calculate(sum('Sales Table'[Amount Ordered]), Calender[Month]=Calender[Month])

which of course does not work. So the question is, how can I set the filter in the CALCULATE function to the @Month Value for each row?

Thanks for your help in advance

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-17T19:54:32

If I understood what you are asking I provide an answer for calculating the Service Level per Month measure. However it is not clear to me how you are going to calculate the average service level per month based on that monthly values since the average will be the same than the Service Level per Month value, in your example the average for month 1 will be 87.5, right?

First create a calculated column called SalesMonth in the SalesData table using the following expression:

= RELATED ( 'Calendar'[Month] )

Note before create the previous calculated column you will have to create a relationship (if doesn't exist yet) between SalesData table and Calendar table using Day column in both tables.

Now create a measure for total amount ordered monthly:

Ordered Monthly :=
  CALCULATE (
    SUM ( SalesData[Amount Ordered] ),
    FILTER (
       ALL ( SalesData ),
       COUNTROWS (
          FILTER ( SalesData, SalesData[SalesMonth] = EARLIER ( SalesData[SalesMonth] ) )
          )
      )

)

The same measure must be created for total amount sent monthly:

Sent Monthly:=
CALCULATE (
  SUM ( SalesData[Amount Sent] ),
  FILTER (
    ALL ( SalesData ),
    COUNTROWS (
      FILTER ( SalesData, SalesData[SalesMonth] = EARLIER ( SalesData[SalesMonth] ) )
        )
    )
)

Finally you can create the Service Level per Month measure using this expression:

Service Level per Month := 
DIVIDE ( [Ordered Monthly], [Sent Monthly], BLANK () )

This is an example of the created measures:

Note days from 1 to 5 correspond with month 1 while 34 and 35 days correspond with month 2.

Let me know if this helps.

Sumif equivalent for Power Pivot Measure Calculations

1 Answers