KPI show data if a filter is selected

[ValKorel]

Hi, 

I have the following table as example:

DATE	SEGMENT	UNIT	PRODUCT	AMOUNT
30/06/2024	RETAIL	AAA	XXXX	200
30/06/2024	MICRO	AAA	YYYY	300
30/06/2024	SME	AAA	ZZZZ	100
30/06/2024	MIDCO	AAA	ZZZZ	400
30/06/2024	RETAIL	BBB	XXXX	700
30/06/2024	MICRO	BBB	XXXX	800
30/06/2024	SME	BBB	YYYY	900

I want in a KPI to show values only for RETAIL segment regardless what I select in a filter pane, except when I select the UNIT I want to show me the RETAIL amount for the selected unit. Now my problem is that when I select MIDCO as segment and BBB as unit in the filter pane the RETAIL data is not shown for the selected unit.

I used the following expression:

Sum({1<DATA_RAP={'$(=Max(DATA_RAP))'}, SEGMENT=, SEGMENT={'SME'}, PRODUCT=,UNIT=P(UNIT)>} AMOUNT)

This is happening only on the unit that do not have all the segment and appeared only if I select a segment that is not in the unit.

[Padma123]

Try This..

sum({1<SEGMENT={'RETAIL'},UNIT=$::UNIT>}AMOUNT)

[Kushal_Chawda]

@ValKorel  I think easy way is to exclude selection of all the required field in set except unit like below

=sum({<DATA_RAP={'$(=Max({1}DATA_RAP))'},SEGMENT={'RETAIL'},PRODUCT=,DATE=>}AMOUNT)

[ValKorel]

It doesn't work because when I select the unit it calculate the sum of total RETAIL for both units.

[ValKorel]

Is not working.

[Kushal_Chawda]

@ValKorel  Don't understand what is not working and what is expectation. If you exclude the selections of the fields in set as highlighted in RED it will not affect the result except UNIT. Do not include Segment & Unit in exclusion.

[ValKorel]

I changed the formula as follow:

Sum({1<UNIT= p(UNIT), DATA_RAP = {'$(=Max(DATA_RAP))'}, UNIT= $::UNIT>} If(SEGMENT= 'RETAIL', AMOUNT))

It work fine, regardless what segment I select but if I select a PRODUCT like 'ZZZZ' the Retail amount is changed. It seems to be the same problem but at product level.

[ValKorel · Accepted solution]

Your formula seems to work well if I made this change:

Sum({1<UNIT= $::UNIT, SEGMENT_FINAL = {'RETAIL'}, DATA_RAP = {">=$(=Max(DATA_RAP))"}>} AMOUNT)