Unfilter a SUMMARIZE table

[volfiedParticipant Started: 8Replies: 14Total: 22]

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?

Thanks for any help you can offer.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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]
)

[volfiedParticipant Started: 8Replies: 14Total: 22]

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.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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]
)
)

[volfiedParticipant Started: 8Replies: 14Total: 22]

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.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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 (
ADDCOLUMNS (
CALCULATETABLE (
All_SalesCycle,
ALL ( DateTable[ACYear], [ACPeriod] ),
DateTable[ACYear] = VALUES ( DateTable[ACYear] )
),
“ACPrd”, RELATED ( DateTable[ACPeriod] )
),
[SalesRep],
[ACYear],
[AcPrd],
“QuotaX”, AVERAGE ( [Quota] )
),
[QuotaX]
)
)

Tom

[volfiedParticipant Started: 8Replies: 14Total: 22]

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.

[volfiedParticipant Started: 8Replies: 14Total: 22]

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.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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 (
ADDCOLUMNS (
CALCULATETABLE ( All_SalesCycle, ALL ( [ACPeriod] ) ),
“ACPrd”, RELATED ( DateTable[ACPeriod] )
),
[SalesRep],
[ACYear],
[AcPrd],
“QuotaX”, AVERAGE ( [Quota] )
),
[QuotaX]
)

[tomallanKeymaster Started: 0Replies: 417Total: 417]

I made the last post before checking emails, please ignore.

Regarding the solution, sometimes what is needed is just to work with another person for a while.

Regarding “trouble”, I am glad to help when and where I can; pleased to hear you have a solution.

Tom

[Author]

Posts