Home Forums Power Pivot Unfilter a SUMMARIZE table

Viewing 10 posts - 1 through 10 (of 10 total)
• Author
Posts
• #3812

Is it possible to unfilter a SUMMARIZE() table? I’m trying to come up with the total quota for the fiscal year within the context of each period (month), so that I can calculate a percent achieved of a rep’s entire quota. I have to use SUMMARIZE(AVERAGE()) to come up with each month’s quota because of the structure of the data.

Here’s what I have:
<pre class=”lang:default decode:true” title=”DAX”>FYQ:=SUMX(
CALCULATETABLE(
SUMMARIZE(All_SalesCycle,
All_SalesCycle[SalesRep],
DateTable[ACYear],
DateTable[ACPeriod],
“QuotaX”,
AVERAGE(All_SalesCycle[Quota])
),
ALL(DateTable[ACPeriod])
),
[QuotaX]
)
[Sorry, I can’t seem to get this code block to display properly.]

This same formula without the CALCULATETABLE() works to produce the monthly quota, but I can’t figure out how to unfilter it. I’ve tried a number of different permutations, including using SUMX(FILTER()) with no luck. Any ideas?

#3820

Hi Volfied,

Would this work (just removed the DateTable[ACPeriod] inside the SUMMARIZE so the granularity is DateTable[ACYear]),:

SUMX (
CALCULATETABLE (
SUMMARIZE (
All_SalesCycle,
All_SalesCycle[SalesRep],
DateTable[ACYear],
“QuotaX”, AVERAGE ( All_SalesCycle[Quota] )
),
ALL ( DateTable[ACPeriod] )
),
[QuotaX]
)

#3826

I’m afraid it wouldn’t. The quota is baked into each line of the data, but differs month to month. I have to do the SUMMARIZE(AVERAGE()) to get a single value per month before I can do any math with it.

#3827

Appreciate the feedback, it helps.

How about something like including [ACYear] in the ALL() to ignore the year from the pivot’s filter context, but using VALUES ( DateTable[ACYear] ) to re-apply the filter on year, as in (the additional checking on sales rep may be extra work for your current pivot, maybe not for others):

=
IF (
HASONEVALUE ( DateTable[ACYear] ) && HASONEVALUE ( All_SalesCycle[SalesRep] ),
SUMX (
CALCULATETABLE (
SUMMARIZE (
All_SalesCycle,
All_SalesCycle[SalesRep],
DateTable[ACYear],
DateTable[ACPeriod],
“QuotaX”, AVERAGE ( All_SalesCycle[Quota] )
),
ALL ( DateTable[ACYear], [ACPeriod] ),
DateTable[ACYear] = VALUES ( DateTable[ACYear] ),
All_SalesCycle[SalesRep] = VALUES ( All_SalesCycle[SalesRep] ),
),
[QuotaX]
)
)

#3829

How are VALUES() and HASONEVALUE() reapplying the filter? The results I’m getting are the same as before.

What really throws me is that ALL(DateTable[ACPeriod]) has no effect. Doing the same with [ACYear] works.

Edit: Okay, “works” may be an overstatement. It has an effect.

#3837

I will explain later about the VALUES() and HASONEVALUE(), but I think there is a more pressing topic to discuss.

I think the SUMMARIZE and CALCULATETABLE are reversed. I think SUMMARIZE is gathering data for the current filter context of the pivot, and the ALL is being applied during the CALCULATETABLE (a little late).

The following formula will probably need to be tweaked because I do not have a dataset to work with, but I think something along these lines will get us closer to a solution (if possible, it would be helpful if could you attach a workbook with some anonymized data to minimize exchanging formulas during testing):

=
IF (
HASONEVALUE ( DateTable[ACYear] ) && HASONEVALUE ( All_SalesCycle[SalesRep] ),
SUMX (
SUMMARIZE (
CALCULATETABLE (
All_SalesCycle,
ALL ( DateTable[ACYear], [ACPeriod] ),
DateTable[ACYear] = VALUES ( DateTable[ACYear] )
),
“ACPrd”, RELATED ( DateTable[ACPeriod] )
),
[SalesRep],
[ACYear],
[AcPrd],
“QuotaX”, AVERAGE ( [Quota] )
),
[QuotaX]
)
)

Tom

#3840

I don’t think it likes this part:

DateTable[ACYear] = VALUES ( DateTable[ACYear] )

It keeps telling me that it’s getting a table of results when only one value is expected.

I’m not sure that I can come up with an anonymized workbook, unfortunately. It’s just too involved. It would certainly make this easier.

#3843

Goshdarnit. I figured it out. It wasn’t anything to do with the measure. I added the Month name to the Columns below ACPeriod. It’s a 1:1 relationship, so I didn’t imagine it would matter.

Thank you so much for your help, Tom. I’m sorry to have put you to this trouble.

#3844

OK.

With the intent of taking steps closer to a solution, let’s exclude the year for right now and try the following (which you may also need to tweak for typos):

= SUMX (
SUMMARIZE (
CALCULATETABLE ( All_SalesCycle, ALL ( [ACPeriod] ) ),
“ACPrd”, RELATED ( DateTable[ACPeriod] )
),
[SalesRep],
[ACYear],
[AcPrd],
“QuotaX”, AVERAGE ( [Quota] )
),
[QuotaX]
)

#3845