Calculate(sum(Filter)) function

Anything related to PowerPivot and DAX Formuale
Fdo.andrade
Posts: 1
Joined: Tue Jul 09, 2019 1:18 am

Calculate(sum(Filter)) function

Postby Fdo.andrade » Tue Jul 09, 2019 1:23 am

Hi everyone,

I´m new in Dax and power pivot, and I have a small problem that i can´t solve. I hope you can help me.

I keep the record of my data per week, so I generate the sistem sales report every friday night. Every month I stablish in another table my month goals divided by week so I can make a comparison of the achivements whit my goal each and every week.

The problem is that I want to create a mesure on my client pivot table, that mesures the diference between the goal of clientes for the current week minus the actual clients reported on the las friday report (last week).

Here is an example of my pivot table.

PTClients.PNG
PTClients.PNG (7.14 KiB) Viewed 83 times


Here is an example of my Dax mesure

=(CALCULATE(sum(MetaTotalMes[#Clientes]),FILTER(MetaTotalMes,MetaTotalMes[#Sem])))-(CALCULATE(sum(Cartera[Clientes]),FILTER(Cartera,[# Sem.]-1)))

Im using calculate and filter to get the sum of clients for each week, the thing is that I need to substract the actual # of clientes of the past week to get the acutal goal form the week.

Example:

Ej.PNG
Ej.PNG (9.37 KiB) Viewed 83 times


I hope someone can help me, great day.

PhilC
Posts: 264
Joined: Tue Sep 09, 2014 8:13 am

Re: Calculate(sum(Filter)) function

Postby PhilC » Tue Jul 09, 2019 9:46 am

Hi Fdo.andrade,

It does sound like you are thinking of creating a calculated column, rather than a measure. Try a search on "powerpivot calculated column vs measure" and read through the results to better understand which to use. Matt's blog post here is a good place to start: https://exceleratorbi.com.au/calculated ... sures-dax/

Assuming you now choose to use measures, perhaps the following might get you moving in the right direction.

Firstly, set up a Date table. See Matt's post here: https://exceleratorbi.com.au/build-reus ... wer-query/
(or create one in Excel to suits your needs as for now, might be easier than learning the more flexible approach above)
Link your two tables to this table - will need an actual date field in each, so maybe pick the last day of the week in question.


Actual = SUM(MetaTotalMes[#Clientes])
This is a basic sum of the column. No need to filter on the week as the visual will do that when you put in the Week field from your Date table.


Goal Clients = SUM(Cartera[Clientes])
Same as for actuals, but for the Goals


Goal Clients Next Week = CALCULATE([Goal Clients],DATEADD(Dates(Date),1,WEEK)) [Edited - had used DATEDIFF by mistake]
This offsets the calculation by a week. So on Week 23 of your visual, Actual will be correct, and this will get the Week 23 + 1 = Week 24 for the Goal.

Then, the difference is as simple as [Goal Clients Next Week] - [Actual]

If you need more specific assistance, post a workbook with data (real or mocked up) and can use that to help.

Cheers
Phil


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests