Alternate States in Pivot Table

[AndyQuirin1]

Hi Team,

I've got an Alternate States comparison use case for a Sales dashboards that I'm building.
The team is asking for a single table where they can grab two different groups of dimensions and compare them against a handful of key sales measures.

Dimensions would be Sales Rep, Region, Deal Closed Date, ID, etc.

Measures would be Bookings, Gross Margin, Gross Margin %

They want the table to be structured where they can apply filters for Alternate State Group 1 and Alternate State Group 2.
Group 1 and Group 2 would be the only columns in the table.
Bookings, Gross Margin, and Gross Margin % would be the measures and make up the rows of the table.
Something like:

-----------------------------|---<Group 1> ---|---<Group 2>---|
---------------------------------------------------------------------------
Bookings |---------------|-------$$$---------|-------$$$--------|
Gross Margin|-----------|-------$$$---------|-------$$$--------|
GM%|---------------------|-------%%---------|-------%%--------|

Does this have to happen in a Pivot Table?
Do you know how I can set all the Group 1 and Group 2 dimensions as the columns?

Thanks for the help!!

[justISO]

Hi, how I would do: First I would create separate 'table' for Measures, like

Measure:
load * inline [
Measure, MeasureNr
Bookings , 1
Gross Margin , 2
GM% , 3 ];

(or you can use ValueList() function, but later you need to define full name of measure, not measure number as I use now).

In Master Items you create 2 new Alternate States, for example 'State1' and 'State2'.

You create Pivot table or Simple Table and add 'Measure' as dimension and 2 measures as your Groups 1&2:

where 'group1' measure looks like:

if(MeasureNr=1, num( sum({[State1]} bookings),'#,00'),
if(MeasureNr=2, num( sum({[State1]} margin) ,'#,00'),
if(MeasureNr=3, num( sum({[State1]} gm)  ,'#,0%')
)))

and 'group2' looks the same, except with 'State2'. Notice that sum depends on alternate state.

At last you create 2 filters, but one must be set to 'State1' (menu ->Appearance ->Alternate states) other to 'State2'. So which filter you will use, that group measure will react.

[AndyQuirin1]

Thanks justISO! I'll try it and report back!

[AndyQuirin1]

So I got it partially working! MeasureNr 1 'Bookings' looks to be working great using the syntax below. I'm unable to get MeasureNr 2 'Product Bookings' to return a value (it returns 0 currently). 

if(MeasureNr=1, //Bookings
num(
SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>} [Adj Total Value])
+
SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>} [Adj Backend Rebate])
,'$#,##0')

,if(MeasureNr=2, //Product Bookings
num(

SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR( [Deal Total Value], Deal_ID))
+
SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR( [Deal Backend Rebate], Deal_ID))

,'$#,##0')
))

Any idea why the syntax for the second returns a 0?

[justISO]

For Product Bookings you are aggregating, so probably this is causing some problems. Try to add 'nodistinct' - usually its fix majority of aggr problems:

SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR(nodistinct [Deal Total Value], Deal_ID))
+
SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR(nodistinct [Deal Backend Rebate], Deal_ID))

[AndyQuirin1]

Thanks JustISO,

Very much appreciate the nodistint tip! 
I've got 14 measures in the table, and it seemed to fix all but 4 of them.

Here's one for Product Bookings (for opportunities that are products). I was using the AGGR function to make sure each Deal ID was only being counted once. I think now with nodistinct, a deal can be counted more than once if it has multiple rows in the table.

,if(MeasureNr=2, //Product Bookings
num(

SUM( {State1 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR(nodistinct [Deal Total Value], Deal_ID))
+
SUM( {State1 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR(nodistinct [Deal Backend Rebate], Deal_ID))

,'$#,##0')

Any idea of what I can change to prevent that duplicaiton?

[justISO]

Let's deconstruct your formula and understand what you are really need to calculate. I made dummy dataset:

load * inline [
Deal_ID, Deal Status, Deal Active Flag, Deal Opportunity Type, Deal Backend Rebate, Deal Total Value
A, won, 0, Product Opportunity, 5, 100
A, won, 0, Product Opportunity, 10, 100
B, won, 0, Product Opportunity, 10, 100
C, lost, 0, Product Opportunity, 10, 100
D, lost, 0, Product Opportunity, 10, 100
E, won, 0, Other Opportunity, 10, 100];

Ignore deal status, flag and type, and only look at deal backend rebate. As you want to 'make sure each Deal ID was only being counted once', which result is correct, according your logic, 55 or 40?

where formulas are according

AGGR( SUM( [Deal Backend Rebate]), Deal_ID)
SUM( AGGR(nodistinct [Deal Backend Rebate], Deal_ID))

[AndyQuirin1]

Thanks justISO,

Specifically with this bit of code:
SUM( {State1 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR(nodistinct [Deal Total Value], Deal_ID))

I want to add up the [Deal Total Value] for all Won, Active = 0, Product Opportunity types.
I want to be considerate to only count each deal 1 time as the data set can have the deals repeat with another dimension (BU). 95% of deals are tied to 1 BU, but each deal can have up to 4 different BUs (and repeated lines) in the data.

In your example, I'd want to add up Deal A + B to get a result of 200. Deal C and D are lost and Deal E is 'Other' so they all should be ignored. You're example data is slightly different than the actual data as Backend Rebate and Deal Total value should be consistent. Something closer to:

DealID, BU, Deal Backend Rebate, Deal Total Value
A, BU1, 10, 100
B, BU1, 10, 100
B, BU2, 10, 100
C, BU2, 50, 500
C, BU3, 50, 500
C, BU4, 50, 500 

[justISO · Accepted solution]

Now I understood, thanks, and after a short investigation, you are right, in this case nodistinct does change the result. But seems aggregation not only by DealID, but by MeasureNr too, provides result (try it, and notice, that I removed nodistinct here, maybe it is not needed in previous formulas also, if addition of MeasureNr do the trick):

if(MeasureNr=2, //Product Bookings
num(

SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR( [Deal Total Value], Deal_ID, MeasureNr))
+
SUM( {State2 <[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Deal Opportunity Type]={'Product Opportunity'}>} AGGR( [Deal Backend Rebate], Deal_ID, MeasureNr))

,'$#,##0')
)

[AndyQuirin1]

Hi justISO,

Very impressed with incorporating MeasureNr - would never have thought to have done that.
It solved the calculation for Product Bookings. I've tried it on the other broken calculation and it's still coming up short. 

Here is the pre-revised calculation:

,if(MeasureNr=11, //Protect Gross Margin
num(
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR(nodistinct [Deal Total Value], Deal_ID))
+
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR(nodistinct [Deal Backend Rebate], Deal_ID))
-
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR(nodistinct [Deal Total Vendor Cost], Deal_ID))
-
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR(nodistinct [Deal Total Service Delivery Cost], Deal_ID))
,'$#,##0')

I've got to add / subtract Deal Total Value, Backend Rebate, Vendor Cost, and Service Delivery Cost to get the Gross Margin number. I also tried with your revision to look like this:

,if(MeasureNr=11, //Protect Gross Margin
num(
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR([Deal Total Value], Deal_ID, MeasureNr))
+
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR([Deal Backend Rebate], Deal_ID, MeasureNr))
-
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR([Deal Total Vendor Cost], Deal_ID, MeasureNr))
-
SUM( {State1<[Deal Status]={'won'}>*<[Deal Active Flag]={'0'}>*<[Practice]={'Protect'}>} AGGR([Deal Total Service Delivery Cost], Deal_ID, MeasureNr))
,'$#,##0')

But the numbers are still slightly off. Any idea why this would be different from the previous?