Dynamic Dimension for AGGR() using Pick(Match)

[PIous_314]

Hello!  

Some Background:  I have a table that is calculating Price/Volume/Mix Effects between a Base and a Comp Period.  The table is dynamic in that the Unit of Measure and Dimension(s) are chosen by the user.  Additionally, the Totals for these effects need to be the sum of the individual effects by dimension (no problem in a straight table as the Totals function can be set to Sum in Properties.

The Problem:  I want to provide this data in a Pivot Table (no Totals function option in properties) and Waterfall chart (no dimensions) and so I am trying to aggregate the sum across the chosen dimensions in the measure formula.

I have tried the following formula, but it is evaluating to zero in the table and the chart:

sum({[Comp Period]}aggr(([PVM Unit] - [PVM Unit Comp])*[PVM Price/Unit Comp],

Pick(Match(GetFieldSelections(DimName_PVM), 'Item Price Group - Grade', 'H0', 'H0B', 'H1', 'H2', 'H3', 'Customer'),
[Item Price Group - Grade],
[H0],
[H0B],
[H1],
[H2],
[H3],
[Customer])

))

I have also tried putting the Pick(Match) part into a variable with no success (but perhaps I am not doing it correctly?!)  Also, this does work as desired if I explicitly name the dimensions for the aggregation, so I am fairly certain the problem is with the Pick(Match).

Hopefully I have given a thorough explanation, but please let me know if more info is needed.  Any suggestions to try would be most helpful and appreciated!  

Thank you!

[Chanty4u]

Try this 

Create a variable in script 

SET vDimPVM = Pick(

    Match(GetFieldSelections(DimName_PVM), 

        'Item Price Group - Grade', 'H0', 'H0B', 'H1', 'H2', 'H3', 'Customer'),

    [Item Price Group - Grade],

    [H0],

    [H0B],

    [H1],

    [H2],

    [H3],

    [Customer]

);

And use that inside aggr()

Sum({[Comp Period]} Aggr(

    ([PVM Unit] - [PVM Unit Comp]) * [PVM Price/Unit Comp], 

    $(vDimPVM)

))

[PIous_314]

Thank you!  I tried this and it doesn't work... the full calculation is returning zero everywhere.  Here are the results of looking at the variable in a few ways:

Text Box (regardless of any dimensions selected or not)

=vDimPVM returns the text of the Pick(Match) formula entered in the script

=$(vDimPVM) returns nothing

Straight Table with single dimension selected (H3)

=$(vDimPVM) returns the individual field values of H3 corresponding to the dimension column

[marcus_sommer]

The dimensions for the aggr() must be a valid field-reference - not an expression-result as string or field.

I think I would go in this direction:

sum({< [Comp Period] > }
   aggr(([PVM Unit] - [PVM Unit Comp]) * [PVM Price/Unit Comp],
   $(=concat('[' & DimName_PVM & ']', ','))))

Beside this make sure that the base-calculation - without the aggr() - and with aggr() and fixed set dimensions - without any dynamic stuff is working like expected. Because there is no further aggregation-function included which means that only() is applied and further there is no exception handling and each non numerical return in any part will set it to NULL.

[PIous_314]

Thank you for your response!

This *almost* works for the case where a single dimension has been selected.  In the case where [PVM Unit] = 0, your formula is evaluating to zero as well (when it should be the negative of the revenue in the comp period).  When I add a measure that is just $(=concat('[' & DimName_PVM & ']', ',')) to the table for these same records, it is showing NULL (gray box with a dash) and the individual field value for the records where the calculation is correct.

When trying with multiple dimensions there are many more records that do not match (although some of them do?) and beyond the case described above, I cannot see a clear pattern to understand why.

I can confirm that the base calculation, both without aggr() and with aggr() using fixed set dimensions does work.  [PVM Unit], [[PVM Unit Comp], and [PVM Price/Unit Comp] are all master items and exception handling happens in the formulas for those.

[marcus_sommer]

Checking the concat() results in an expression is a good idea but then without the $-sign expansion - just:

concat('[' & DimName_PVM & ']', ',')

and also similar to various measure-fields, like:

concat(distinct [PVM Unit], ' + ')

[PIous_314]

Is there something specific I should be checking for?  This doesn't change the suggested solution not working in all cases.

Thank you!

[marcus_sommer]

It wasn't meant as solution else as a way to detect some of the possible causes underlying the not expected results, for example if any of the concat() results of the measure-fields in regard to the chosen dimensionality is NULL or returned not a single value else n concatenated values it would mean that the data-set and/or the associations within data-model aren't suitable for your view.

If the concat() discovered any invalid data/associations you should look directly on the data by pulling all relevant fields into a table-box - and then selecting the data-set to those parts which return not the wanted results and then you will very probably find some NULL and/or missing associations and/or n different values per dimensionality. You need to know your data-set exactly before you could adjust the object-views and/or the data-set.

[PIous_314 · Accepted solution]

Just to follow up, here is how I ended up solving this problem:

I never was able to get Pick(Match()) to work properly (there still may be a way, I just wasn't able to sort it out) so I went a different route.  I added a field to the DimPVM table that includes the expression as below:

Then I used DimExpression_PVM in the Filter Pane for the user to select and now the measure formula I use is: 

sum({[Comp Period]}aggr(([PVM Unit] - [PVM Unit Comp])*[PVM Price/Unit Comp],

GetFieldSelections(DimExpression_PVM)))

This works everywhere all of the time as desired with the Totals function (in Straight Table with Totals function Auto, in Pivot Table with no Totals function choice, and a Waterfall chart with no dimension).

Thank you to everyone, I still learned a lot from your responses!