Home Forums Power Pivot Unfilter a SUMMARIZE table

This topic contains 9 replies, has 2 voices, and was last updated by  tomallan 4 years, 7 months ago.

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

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 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.

    #3820

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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]
    )

    #3826

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 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.

    #3827

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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]
    )
    )

    #3829

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 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.

    #3837

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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

    #3840

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 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.

    #3843

    volfied
    Participant
    • Started: 8
    • Replies: 14
    • Total: 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.

    #3844

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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]
    )

    #3845

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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

Viewing 10 posts - 1 through 10 (of 10 total)

The forum ‘Power Pivot’ is closed to new topics and replies.