How to create Filter Pane and KPI by Measurement

[apichana]

Hi, 

My raw data look like below.  I created master item for Measurement as below.  

(sum({<[PRODUCT_NAME]={'GASOHOL E20', 'GASOHOL E85'}>} [QTY_LITRE]) / sum(QTY_LITRE)

to represent %E20orE85 Consumption per User Name.

Date	User Name	Product_Name	QTY_LITRE	%E20orE85Consumption
01/01/2021	AAAA	E20	20
02/01/2021	AAAA	E85	30
03/01/2021	AAAA	DIESEL	15
01/01/2021	BBB	DIESEL	10
02/01/2021	BBB	DIESEL	20
03/01/2021	BBB	DIESEL	30

The question is 

1. How can I have a filter pane on Dashboard to show as below criteria 

Definition	E20 or E85Consumption
ACTION NEEDED	E20 or E85 = 0 %
Need Improvement	E20 or E85 < 80%
GOOD	E20 or E85 > 80%
EXCELLENT	E20 or E85 = 100%

2. How can I create each KPI to count no. of transaction  as above criteria. 

Please give me an advice. Thank you.  

[apichana]

I have calculation below as a master measurement which  show % of E20 and E85 Consumption compared with total QTY_LITRE .  How to convert this measurement to a dimension? 

 sum({<[PRODUCT_NAME]={'GASOHOL E20', 'GASOHOL E85'}>} [QTY_LITRE]) / sum(QTY_LITRE)

[Ksrinivasan · Accepted solution]

hi,

find the script

Gas:
LOAD
"DATE",
UserName,
Product,
"QTY(L)"
FROM [SSSS1.xlsx]
(ooxml, embedded labels, table is gasolain)
where WildMatch(Product,'GASOHOL*');

hi:
Load
Product as TProduct,
Sum("QTY(L)") as Tot
Resident [Gas]

Group by Product;

result and expression

further you use if condition to get classification of Definitions,

ksrinivasan

[apichana]

Hi Ksrinivasan, 

Thank you so much for your advice.  In order to get classification of definitions, I have tried creating master dimension as expression below but it doesn't show out and  I got invalid dimension.  Please advise. 

= If(sum({<[PRODUCT_NAME]={'GASOHOL E20', 'GASOHOL E85'}>} [QTY_LITRE]) / sum(QTY_LITRE) = 1, 'Excellent','ACTION NEEDED')

[Ksrinivasan · Accepted solution]

hi,

use this expression as Measure

=if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))='0','Action Need',
if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))>'0' and
((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))<'80','Need Improvement',
if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))>'80' and
((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))<'99','Good',
if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))='100','Excellant',''))))

result

ksrinivasan

[apichana]

Hi, 

It works!!  Thank you so much. Really appreciate for your help. 

However, with this expression it can't be used as a dimension right? So is there any way to do the dimension in order to filter by classification like excellent, good,...