9 Replies Latest reply on Aug 24, 2016 10:04 PM by naveen.yanamadala

# calculated logic

Hi,

I need all your help to create a logic/calculated field for my chart

In the  below chart #1:  The 2 lines showing are transaction% and Amount % fields . I have bank field as a muti value dropdown filter with values as : All, A,B,C, D E,F,G.H..

If we select  multiple banks say like A and B then i should get 4 lines and also if i select bank C then i should get 6 lines  as shown in chart #2 ( I achieved this by keep bank field in color shelf

My requirement is if i select ALL from the bank filter  then I need to get only 2 lines with the total of all banks A,B,C, D E,F,G.H..

Please let me know your thoughts .

chart#1 Chart #2: • ###### 1. Re: calculated logic

When you use the Bank Field on the Color Mark, it will show one line each for all of the selected banks. I don't think you can avoid showing all banks when you select ALL in the filter.

If you are looking to aggregate the two fields (Transaction % and Amount %) when all Banks are selected, then you will need to setup a calculated field. You can then create a separate chart for the aggregated value.

What is on your x-axis in these charts?

If you can post your data, it will be helpful.

• ###### 2. Re: calculated logic

Hi Naveen,

Thanks for the reply. Could you please let me know how the logic should be written for the calculated field for all banks and is there anyother way to show single multiple and all selection in the same chart.

Thanks,

• ###### 3. Re: calculated logic

Unfortunately, the filter alone cannot give you this functionality, but using a parameter you can get approximately what you are after.

As in the attached workbook, create a parameter to switch between a comparison mode, in which all banks will have two lines for your two measures, and a total mode, in which there will be two lines which represent the total across all selected banks.

Then create a calculated field for each of your measures, like below for 'Amount'.

IF [Bank parameter] = 'Total'

THEN {EXCLUDE [Bank] : SUM([Amount])}

ELSE {INCLUDE [Bank] : SUM([Amount])}

END

Basically the field is switching between providing sums for an individual bank and a sum over all the banks, dependant on the choice of the parameter. The {}s indicate a Level of Detail (LOD) calculation. If you are not familiar with them, they are wonderfully useful, and summarised well here: Overview: Level of Detail Expressions

In implementing this chart, I would also recommend:

• Having a calculated field for the tooltip, so that if 'Total' is selected, the tooltip will read 'All banks'; and
• Having dual and synchronised axis (you can hide the right-hand one by making it white, if you want).

Hope this helps, do let me know if you have any questions!

1 of 1 people found this helpful
• ###### 4. Re: calculated logic

I tried this method and it seems to work. But you have to select ALL banks in the Bank filter when selecting "Total" as the parameter. Otherwise, it does not aggregate the right values.

I wonder if there is a way to link the Parameter values to the Filter selection. If ALL is selected, then the Parameter should automatically be selected as "Total". If not, then the other parameter value should be selected.

• ###### 5. Re: calculated logic

Parameters don't have this behavior. They can't be changed by actions.

• ###### 6. Re: calculated logic

Hi Jessica,

Thanks for the reply......  I am trying to make my chart with the changes mentioned by you but I am unable to open the  workbook since i am using  version 9.2

• ###### 7. Re: calculated logic

Ah, that's a pain. In that case, to set up what was in the workbook:

• Create a parameter, e.g. 'Bank parameter', and give it two possible values, e.g. 'Total' and 'Comparison'
• Show parameter control (from the context menu by right-clicking the newly created parameter
• Create a calculated field for each of your measure values (i.e. transaction and amount), like the following:

IF [Bank parameter] = 'Total'     THEN {EXCLUDE [Bank] : SUM([Amount])}ELSE {INCLUDE [Bank] : SUM([Amount])}ENDThen set up the chart:

• Your x-axis in the columns shelf
• The new calculated Amount and Transaction fields in the rows shelf, each aggregated by SUM
• Make Amount and Transaction dual axises (see below) and synchronise them (by right-clicking the right-hand axis and selecting from the menu)
• Untick 'Show header' on the right-hand axis
• Add a bank filter and 'Show filter' Beyond aesthetic stuff ('Measure names' are there as a label as without it amount and transaction were indistinguishable on the 'Total' mode, and there's also the calculated tooltip mentioned before) that should be it.

1 of 1 people found this helpful
• ###### 8. Re: calculated logic

Hi Jessica,

I tried the below logic as u said and having the below error

IF [Bank parameter] = 'Total' THEN {EXCLUDE [BANK_NAME] : SUM([CUM_FRAUD_AMT %])}

ELSE {INCLUDE [BANK_NAME] : SUM([CUM_FRAUD_AMT %])}END

error: Argument to sum(an aggregate function) is already an aggregation, and cannot be further aggregated

Note: Amt and tran fields are cumulative fields

and the formula which i used to create cum_fraud_amt% is : running_sum(sum([fraud_amt])) / Total(sum([Fraud_amt]))

• ###### 9. Re: calculated logic

Hi Jessica,

I still couldn't crack the  issue which i posted in the above post . I am still struggling to get a logic for the cumulative field i am using.