Percent Total on a ranked dimension

[NickHoff]

My pivot table shown below has two dimensions, with the first being the ProcedureSurgeonName.

I have the following expression calculating my ranked dimension, which is the second dimension.

aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)

My expressions are the following:

Supply Total Cost:  SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)

and 

Percent of Total:   SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)/SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL SupplyChainLUOMTotalCost)

As you can see in the table above my percentage of total is displaying across all Physician's so i've changed it to aggr on the Vendor Name, using the following.

SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)/SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL <VendorName>SupplyChainLUOMTotalCost)

But, my totals still aren't displaying properly, it should be 100% for each Physician total and then each vendor will add up to the 100%.  I'm using the calculated dimension above for the vendor, but this is how my totals display when aggr by vendor.  

I've also tried the following which yields no results:

SUM({<VendorName = {"=aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)"},ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)

/

SUM({<VendorName = {"=aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)"},ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL <VendorName>SupplyChainLUOMTotalCost)

How can I get the % of Total column to display the percentages by my calculated dimension?

[kaushiknsolanki]

Hi,

Your  expression should be

SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)/SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL <ProcedureSurgeonName>SupplyChainLUOMTotalCost)

[NickHoff]

I tried that as well and it gives 100% for every vendor, i think the issues is just doing an aggregation on the TOTAL won't work because it's a ranked calculated dimension.

[sunny_talwar · Accepted solution]

How about this

Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost)
/
Aggr(NODISTINCT 
  Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost)
, ProcedureSurgeonName)

[NickHoff]

Thank you Sunny,

Any chance you can explain to me why Aggr(NODISTINCT works and creating the Aggr the other way didn't?

[sunny_talwar]

Where else did you try Aggr() within your set analysis? 

[NickHoff]

I tried using the following which the aggr was inside the set instead of outside, which that makes sense.  

SUM({<VendorName = {"=aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)"},ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)

/

SUM({<VendorName = {"=aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)"},ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL <VendorName>SupplyChainLUOMTotalCost)