topic Pivot Table Set Analysis dividing sum by alternate record which must ignore dimension. in App Development

Pivot Table Set Analysis dividing sum by alternate record which must ignore dimension.

rougeherring — Sat, 16 Nov 2024 18:07:35 GMT

I am creating a Pivot Table that lists multiple records.

Within a measure I need set analysis to divide a total based on 1 dimension by a total which is a unique record but must also utilize a second dimension.

-The total is it's own separate record and cannot be a sum.
-The pivot table has 1 dimension that must be included in the calculation.

= Sum({$<[FlagOne]={1},[Market] = {"$(vMarket)"},[FlagTwo]={1},
[ProductID_Table] = {"$(=MaxString(ProductID_VariableGroup_ID))"}>}[Sales])

Sum(TOTAL{
$<[FlagOne]={1},[Market] = {"$(vMarket)"},[ThisRecordIsATotalFlag]={1},
[ProductID_Table] = {"$(=MaxString(ProductID_VariableGroup_ID))"}>}[Sales])

The numerator is working however the denominator is ignoring a dimension. There is another dimension in the pivot table (Product Type) that I need to be different for each group. How can I tell the total set analysis to give me the total based on a dimension in the Pivot Table.

Re: Pivot Table Set Analysis dividing sum by alternate record which must ignore dimension.

Kushal_Chawda — Wed, 09 Sep 2020 22:46:13 GMT

@rougeherring add that dimension in total keyword. You can include your actual dimension name as I have assumed it

Sum(TOTAL <Product>{
$<[FlagOne]={1},[Market] = {"$(vMarket)"},[ThisRecordIsATotalFlag]={1},
[ProductID_Table] = {"$(=MaxString(ProductID_VariableGroup_ID))"}>}[Sales])

Re: Pivot Table Set Analysis dividing sum by alternate record which must ignore dimension.

rougeherring — Thu, 10 Sep 2020 01:24:02 GMT

Unfortunately that didn't work. I added the <[Dimension2]> as <[Product]> after TOTAL and now it's calculating all zeros.

The pivot table has 2 dimensions, one of which I have moved above the measures to group them ([Product]). Unfortunately, it is not breaking giving me a different total for each product based on that dimension. It's giving me the sum of all 4 products together.