KPI Visual Calculation Help

I have a program, and some programs have subprograms. I need to build a calculation that adjusts the KPI visual numbers accordingly. For example, the program “Del” has two subprograms, “Task1” and “Task2,” with a total budget of 1,264,000. When I select the program “Del,” the KPI should display 1,264,000. If I filter by subprogram and select “Task1,” it should display 1,234,000.

I was able to build a calculation for a table using this formula:

sumOver(max(budget), [subprogram, program], POST_AGG_FILTER)

However, this isn’t working for KPI visuals. Could you help me adjust this calculation for KPI visuals?
qwes

Hi @hellosai321,
What result are you getting currently on your KPI when utilizing the calculated field that you tried to build?

Are you using parameter controls setup for your program and subprogram?

Hi @hellosai321 ,

Can you provide us with a screenshot? does it show any error? or the values are not matching?

Thanks,
Pradeep S

Hi Brett,

Yes, my controls are parameterized.

While using same calculations for KPI it shows syntax error as program and subprogram are not adding in field values…

Hi @hellosai321 ,

Since you are using level aware calculation, the kpi visual must be looking for calculation reference and throwing similar error. One workaround to this is creating a pivot table with all the related fields and then hiding these fields. Also, this workaround has a limitation of not being able to increase font size though. If you can share sample data in the Arena can try helping with a sample workaround.

However, I am curious to know whether the data granularity is different in the dataset. As I see you are using a level aware calculation with max at subprogram level, are there multiple records for each program and sub-program tuple? If not we may not need this level aware calculation.

Hope this helps.

Thanks,
Prantika

Hi @prantika_sinha ,

Thank you for your response. I’ve attached a sample screenshot below that shows the data where I was able to generate the desired output in a pivot table, displaying the unique budget for each program and its subprogram using the “Test” field. However, when I try to use the “Test” field in the KPI, I encounter an error. I’m unsure how to achieve the same result for the KPI.

Captureq
Actual Data

Budget Analysis

I modified the kpi visual to a pivot table to get similar KPI visual. But as mentioned earlier, the font size configuration is a challenge here.

However, since the reason to implement level aware calculation was not clear, I also configured another KPI visual with the budget field and same set of filters. Please check out which one suits your use case!

sample solution - 36559

When filtering for the program “con_one,” the KPI should display the sum of its unique subprogram budgets, which is 810,000. If a specific subprogram of “con_one” is selected, the KPI should display only the budget for the selected subprogram.

Hi @hellosai321 ,

I have republished the analysis. Let me brief what was the miss.
Since I am using pivot table, I have created a dummy string to pivot the values against and have collapsed all fields and then disabled the ± controls from the pivot.

In the previous version I missed collapsing all row fields, so the number continued to appear in 2 rows.

For the KPI visual, created a RANK of user id based on program - sub program to limit records and filtered users with rank 1. This will not work if user# to prog-subprog mapping has many to one relationship, like same user id reported against the program-sub program tuple.

Let us know if this meets the requirement!

Thanks,
Prantika

Hi @prantika_sinha ,

It worked for my current scenario, but what if we have the same resources repeated in other programs? I have a scenario like this. How to build ranking for this as I don’t unique fields in the dataset.

The LAC way of calculation and pivot table option will still work for that case.