Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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])
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.