Pareto Count with 2 Dimensions?

[mrthomasshelby]

Hi all,

I have a pivot table as shown below:

In this, I'm trying to calculate 80% Potential savings for each Supplier and the number of dimension values(controlled by the panel on the left, 'Levels') that make up this 80% savings. I have written a formula for this as follows:

=Count(Distinct

Aggr(

If(

Rangesum(Above(

Sum(Price_Red)/Sum( total Price_Red)

,1,RowNo()

))<=0.8,$(vDim1)),

($(vDim1),(=Sum(Price_Red),Desc))

)

)

where 'Price_Red' is the savings value and $(vDim1) is the variable controlling the dimension values that are needed to be counted.

But the thing is, I want to display this count by each Supplier in the table and right now, this only happens if I select each supplier individually from the table.

I tried looking into Sunny's answer here and modified my formula but still doesnt give the required output. Please help me include the Supplier dimension in this calculation.

Attaching the qvf for your ref. Thanks in advance! @sunny_talwar @tresesco 

[sunny_talwar · Accepted solution]

So, I had to change few things around in the dashboard.... the new expression that works is this

Sum(Aggr(
  If(Rangesum(Above(Sum(Price_Red)/Sum(TOTAL <Supplier> Price_Red), 1, RowNo())) < 0.80, 1, 0)

, Supplier, ($(='[Supplier-' & vDim1 & ']'),(=Sum(Price_Red), Desc))))

But in order to do this, I had to create new fields in the script

New_Baseline_Final_T8:
LOAD *,
	 [Supplier] & [Specification 1] as [Supplier-Specification 1],
	 [Supplier] & [Specification 1] as [Supplier-Specification 2],
	 [Supplier] & [Specification 1] as [Supplier-Specification 3],
	 [Supplier] & [Specification 1] as [Supplier-Specification 4],
	 [Supplier] & [Specification 1] as [Supplier-Specification 5],
	 [Supplier] & [Specification 1] as [Supplier-Specification 6],
	 [Supplier] & [Specification 1] as [Supplier-Specification 7],
	 [Supplier] & [No.] as [Supplier-No.],
	 [Supplier] & [OPU] as [Supplier-OPU],
	 [Supplier] & [Package Name] as [Supplier-Package Name],
	 [Supplier] & [Product] as [Supplier-Product]
Resident Baseline_Final_T8;

DROP Table Baseline_Final_T8;
RENAME Table New_Baseline_Final_T8 to Baseline_Final_T8;

[mrthomasshelby]

Thanks Sunny! So the only way to achieve this is to Concat all the possible fields with supplier. Appreciate your help as always!

[sunny_talwar]

In order to get the correct sort order, you will need to concat them unless a particular field won't repeat for multiple supplier. For example, I found that some of the Specification 1 were repeating for multiple Supplier. But if Specification 2 is unique to a supplier, then you don't have to combine it with supplier. But doing it won't do any harm.