Displaying KPIs by rating

[Amit_B]

Hi experts,

In our model there are 5 important KPIs, each one of them is presented as percentages and in a Gauge Chart. The model is constantly updated and the change in values can be extreme.
We want to dynamically display on the sheet only the 3 with the lower value, each in a separate Gauge Chart (or other object).

For example,
The values are: kpi1=75%, kpi2=79%, kpi3=54%, kpi4=81%, kpi5=64%
There will be 3 Gauge Charts on the sheet - the first one will display kpi1, the second one will display kpi3 and the third one will display kpi5.

How can this be done?

Thanks.
Amit.

[JHuis]

This is possible, only problem is without a sample data set it is tough. 

Can you maybe give a example data set?

[Amit_B]

Thanks for the reply but I'm not sure how that would help...
There is not that much connection between the KPIs, each one of them measures something different.

This is a digital store.
The first KPI measures the percentage of products sold out of all products.

Item Code	Item Name	Extra Data
111	aaa	...
222	bbb	...
333	ccc	...

Order ID	Order Date Time	...	Item Code	Units	...
1001			222	2
1002			222	1
1002			333	2
1003			333	4

kpi1 = 2/3 = 67% (only 2 items were sold from a pool of 3 items)

Additional KPIs refer to customers, different amounts of money, satisfaction, etc.

[Gabbar]

Lets Say there are two Column KPI and percentage,
Now you can Use rank Function To Rank KPIs on the basis of the percentage or if require another measure in the order(Ascending or descending) you want.
After that you can use your measure using 
Sum({<Dimension={"=('RankCalculatedFunction') = 'n')"}>}Dimension)  where n can be 1,2,3 On the basis of your requirement.

If anymore query please add a sample data set and the required output

[Amit_B]

Great, I thought it should be done in a script, but how can I create such a table that the percentage/value column is calculated separately for each row and is based on data from other tables?
Would it be correct to use variables?
I would love to receive an example.

[Gabbar]

Could you provide a sample Data for your case, which contains enough data to calculate because the previous data set you gave wasnt enough to do the calculation.

[Amit_B]

Let's take a simple example because the big problem for me is to create the KPIs table.
Let's say there are 2 tables, the first table is the stock and the second table is the sales table. There are 2 products and we need to calculate the percentage of items sold from each product, and to show the lower KPI value.

ProductID	ProductName	CurrentStock
001	A	10
002	B	5

OrderID	ProductID	QuantitySold
00001	A	3
00002	A	2
00002	B	1
00003	B	2

So,
KPI_A = (3+2) / 10 = 0.5 = 50% ---> Rank 2 ----> to show in the chart.
KPI_B = (1+2) / 5 = 0.6 = 60% ---> Rank 1

Thanks alot!

[Ahidhar]

try this 

tab2:
mapping load
ProductID,max(QuantitySold) as maxsold
resident tab1 group by ProductID;

tab:
load ProductID,ProductName,CurrentStock,ApplyMap('tab2',ProductID)/CurrentStock as kpi;

and in gauge chart

min(kpi)*100